texts: List chronological pageviews without adjacent duplicates in PostgreSQL

texts:
I've got a log of pageviews. Multiple pageviews with the same path may occur next to each other if say the user refreshes the page.

To see the user's journey through the site I'd like to select pageviews for that user but eliminating duplicate adjacent pageviews for clarity.

Example simplified table (time is really a timestamp):

CREATE TABLE pageviews(user_id, time, path)
AS VALUES
  ( 1, 1, '/home' ),
  ( 2, 2, '/home' ),
  ( 1, 3, '/profile' ),
  ( 1, 4, '/profile' ),
  ( 2, 5, '/dashboard' ),
  ( 1, 6, '/home' ),
  ( 2, 7, '/profile' ),
  ( 1, 8, '/profile' );

Now assume there's going to be a WHERE user_id = 1 clause and ORDER BY time ASC so we have:

( 1, 1, '/home' ),
( 1, 3, '/profile' ),
( 1, 4, '/profile' ),
( 1, 6, '/home' ),
( 1, 8, '/profile' );

But what I'd like is the duplicate adjacent /profile to be ignored like so (a count of the "grouped" paths would be nice too):

| user_id | time | path     | count |
| ------- | ---- | -------- | ----- |
| 1       | 1    | /home    | 1     |
| 1       | 4    | /profile | 2     |
| 1       | 6    | /home    | 1     |
| 1       | 8    | /profile | 1     |

A simple GROUP BY wouldn't do it as /profile is still in there twice, I only want to "group" adjacent equal values of path.

Also I'm not sure whether the first or last of the equal values should be returned, I don't really mind so whichever makes sense / makes for the simplest query. I'll probably flip it to be DESC in real use but this is how I've written the tables.

Reference I found this post that does basically what I want (included below for permanence). So I'm interested to hear 2 improvements:

Can that query be simplified (for PG 9.4)? One comment mentions DENSE_RANK and WITHIN GROUP to eliminate subquery but I couldn't figure out how to do that.
Can a count of the "grouped" values be added easily (possibly using a window function)?

So any elegant query that can achieve both of those things would be ⭐ I couldn't find any other articles or questions about this but I feel it must be a common thing to do so any other links welcome.

SELECT w1.day, w1.rainy
FROM (SELECT
    w2.day, w2.rainy,
    lead(w2.rainy) OVER (ORDER BY w2.day DESC) as prev_rainy
    FROM weather w2
    ORDER BY w2.day DESC
) as w1
WHERE w1.rainy IS DISTINCT FROM w1.prev_rainy
ORDER BY w1.day DESC;


texts: SQL Server asks for 100GB for a dump with 1GB

texts:
Is it acceptable that SQL Server asks for 100GB free disk space to restore a 1GB dump file?

If no, what should be parameterized?


texts: Using like over charindex to search for pattern

texts:
We where using charindex to search for a string as shown below

SELECT *
FROM tablename
WHERE ( Charindex('47a%$.abc',CAST(columnname AS VARCHAR(MAX)))>0 )

I am planning to replace charindex with LIKE as i require to search only whether the pattern exists not the position.
So i want to know is these any disadvantage in using LIKE over charindex for my case.I am using charindex in several cases. So i want it to work for all sql datatypes ,all characters including special characters(including % (as like already uses %pattern%).So please tell me is there any disadvantage in using like over charindex


texts: mysql8 gives "Access denied for user" error with php but not navicat/workbench

texts:
I have been reading billions of answers/questions for that same error :

SQLSTATE[HY000] [1045] Access denied for user 'root'@'ALEXPC' (using password: YES)

I myself even handle this error like hundred of times during the past 10 years.

But this case is weird : both navicat and mysql workbench can connect to this mysql 8 ( and browse things ) just fine. Just my php code (which worked well on mysql 5.7) refuses to cooperate. Same user/password.

One more strange thing, my php machine name is capitalized in the error message after the first time:

  //    first
     SQLSTATE[HY000] [1045] Access denied for user 'dev'@'alexpc'
      // then
 SQLSTATE[HY000] [1045] Access denied for user 'dev'@'ALEXPC'

And mysql "users and Previleges" tool says "Mysql only allows lowercase characters for hostnames, the account host will be updated accordingly".

P/s: I am using nginx-cgi on windows and php 7.3.4 php_pdo

I also set as recommended by Php website:

 default_authentication_plugin = mysql_native_password


texts: Dog slow query, needs optimizing, please help

texts:
I found this query in our web app that has been made by hired programmers. It performs like a dog and needs optimization, but I'm no MySQL wizard (yet), unfortunately. Please help, anything is appreciated.

This is the query:

SELECT DISTINCT test_user_info_test.ID
FROM test_user_info_test, test_user_saver_test, test_user_filter_test
WHERE test_user_info_test.ACTIVE = '1'
   AND (
         ( test_user_info_test.BALANCE >= '1' AND test_user_info_test.ID IN
           (
              SELECT USERID
              FROM test_user_filter_test
              WHERE symbol = 'x'
                     AND INCLUDE ='1')
           )
        OR
           (
        HAS_IT = '1' AND EXPIRE > '1337500000'
        AND
        test_user_info_test.ID IN
              (
                 SELECT USERID
                 FROM test_user_saver_test
                 WHERE symbol = 'x'
                      AND INCLUDE = '1'
              )
          )
     )

Currently, the tables don't have any indexes.

Description of what the query is supposed to do:
it needs to select the user id's from active users that have a positive balance and have set  a filter for symbol X, in this example. Also it needs to select the user id's from users who have a special subscription that hasn't expired yet and have set a filter for symbol X.

I think the execution of the query can take up to quite a few minutes, which is clearly unacceptable.


texts: Accumulate values into an array with Postgres

texts:
Currently I have this query:
select
    sum(case when my_table.property_type = 'FLAT' then my_table.price else 0 end) as Flat_Current_Asking_Price,
    sum(case when my_table.property_type_mapped = 'SEMIDETACHED' then my_table.price else 0 end) as Semidetached_Current_Asking_Price
from
    my_table;

So if my_table has the values:
property_type | price
--------------+-------
FLAT          | 5000
SEMIDETACHED  | 9000
FLAT          | 6000

the query will return:
Flat_Current_Asking_Price | Semidetached_Current_Asking_Price
--------------------------+-----------------------------------
11000                     | 9000

How can I replace the sum to accumulate the values into arrays to get?
Flat_Current_Asking_Price | Semidetached_Current_Asking_Price
--------------------------+-----------------------------------
{5000, 6000}              | {9000}


texts: Cannot use GRANT ALL anymore on RDS MariaDB 10.5 but can on 10.2

texts:
Using the default admin user I created on a new RDS instance with MariaDB engine 10.5 I cannot use the GRANT ALL anymore and instead get "access denied". This works fine on the 10.2 instance I am upgrading from.
mysql> grant all on `database_name`.* to `database_user`@`%`;
ERROR 1044 (42000): Access denied for user 'master'@'%' to database 'database_name'

The privileges for the default RDS master user are:

I see a report here asking the same question https://forums.aws.amazon.com/thread.jspa?messageID=950604 (Unable to grant table level privileges - Error 1044).


texts: How to set column DEFAULT using value of a variable

texts:
In a script for setting up a database which uses random UUIDs for primary keys, I have:

CREATE TABLE alpha (id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), value INT);
CREATE TABLE beta (aref UUID REFERENCES alpha (id));

INSERT INTO alpha (value) VALUES (42);

Then over a foreign key, to have that record in "alpha" be the default target for insertions into the "beta" table table:

DO $$
    DECLARE l_id UUID;
    BEGIN
        SELECT alpha.id FROM alpha INTO l_id;
        ALTER TABLE beta ALTER COLUMN aref SET DEFAULT l_id;
    END;
$$ LANGUAGE plpgsql;

ERROR:  column "l_id" does not exist
CONTEXT:  SQL statement "ALTER TABLE beta ALTER COLUMN aref SET DEFAULT l_id"
PL/pgSQL function inline_code_block line 1 at SQL statement

How do I use the value of a variable as the default value for a column?


texts: Get all CONVERT(datetime) styles (formats)

texts:
I know this is kinda stupid to ask but is there a Transact-SQL function or anything that returns all CONVERT(datetime) styles (formats)? Something like fnDateFormats will return all date formats:

Output:

Dateformat | SQL code
MM/DD/YYYY | 101

and so on.


texts:
I have SQL Server Express running on one (virtual) machine running Windows 8.1, but I cannot connect to it from another.

I have followed the instructions on https://msdn.microsoft.com/en-us/library/ms191294.aspx, which is pretty similar to countless other instructions, restarted the server, restarted the machine, and, for good measure, made another coffee.

I know I can connect to the other machine using another protocol. I ran HFS on the machine (a nifty little HTTP server), and could easily connect from another machine.https://msdn.microsoft.com/en-us/library/cc646023.aspx to allow access through the firewall, but even with the firewall totally turned off, it doesn’t work.

My settings in Configuration Manager are:

Protocols for SQLEXPRESS | TCP/IP
    Protocol: Enabled Yes; Keep Alive: 30000; Listen All: Yes
    IP Addresses | IP2: Active Yes; Enabled Yes; IP Address …;
                        Dynamic Ports 0; TCP Port [empty]

How can I access this server from the network?

Thanks


texts: I would like to read a book about MySQL database with emphasis on performance and optimization on Linux server. I appreciate your suggestions.


texts: Will longer DB transactions make calls to other DB wait?

texts:
I have a fundamental question but unable to find precise information online.

I opened up a DB transaction (SQL Server) and making multiple inserts, updates, delete etc; the entire process takes about 5 hours to complete (Due to the volume of Data; Assume this is valid).

Now my question is,

All the above happens within the scope of a transaction.  Will this longer transaction make calls to other DB on the same server wait OR disturb them in anyway? My understanding is, this will impact only the calls made to the concerned DB and NOT other databases under the same instance.

Comments / responses and greatly appreciated.


texts: Copy large table to archive table SQL Server 2012

texts:
I have a table with logs of my application that have 161.626.718 rows (and growing) and it's very slow to make a insert on it.
My idea is to keep only 6 months of log in the table and all older records I want to copy on a archive table that is not accessible by the application and if anyone need some info, just do a select direct on the table.
So, the idea is to create a daily job like this:

SELECT * INTO audlog_backup FROM audlog WHERE XDATE <= DATEADD(day,-185,GETDATE())

DELETE FROM audlog WHERE XDATE <= DATEADD(day,-185,GETDATE())

But because of the size of the table, this will take ages and will slow down or even lock the database for some time on the first run, after all most of the rows will be older then 6 months.

Whats the recommendation for doing it safely and faster as possible for the first time?

More information:

SQL Server 2012 Standard

Script of the create table:

CREATE TABLE [dbo].[audlog_backup](
    [PVKEY] [float] NULL,
    [DKEY] [varchar](14) NULL,
    [XDATE] [datetime] NULL,
    [XTIME] [varchar](8) NULL,
    [XFROM] [varchar](50) NULL,
    [XTO] [varchar](50) NULL,
    [DETAILS] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
SET ANSI_PADDING ON
ALTER TABLE [dbo].[audlog_backup] ADD [XUSER] [varchar](50) NULL
ALTER TABLE [dbo].[audlog_backup] ADD [PEOPLEKEY] [int] NULL
ALTER TABLE [dbo].[audlog_backup] ADD [ADDRESSKEY] [int] NULL
ALTER TABLE [dbo].[audlog_backup] ADD [XFILEKEY] [varchar](50) NULL
ALTER TABLE [dbo].[audlog_backup] ADD [XLOGNO] [int] IDENTITY(1,1) NOT NULL
ALTER TABLE [dbo].[audlog_backup] ADD [XTABLE] [varchar](50) NULL
ALTER TABLE [dbo].[audlog_backup] ADD [XFIELD] [varchar](50) NULL
 CONSTRAINT [PK_AUDLOG_backup] PRIMARY KEY NONCLUSTERED
(
    [XLOGNO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


texts: Can I export a maintenance plan without using Integration Services?

texts:
I'm trying to export a simple maintenance plan from an SQL Server instance.

I want to check the export into source control, and apply the exported maintenance plan to functionally identical instances.

StackOverflow and SQL Server Newbie recommend using Integration Services to export the maintenance plan.

When I try to connect to Integration Services on the Export target, I receive the following error:

  Connecting to the Integration Services service on the computer
  "WEBSERVER" failed with the following error: The specified service
  does not exist as an installed service.

We chose to disable Integration Services on WEBSERVER because we use this box only for serving data to consumer applications. All data on WEBSERVER is replicated from a backend instance. Integration Services is used heavily for processing data on the backend instance.

Is there a documented way to export a maintenance plan without using Integration Services? Does Microsoft support it?


texts: How do I make an index for an OR query in MongoDB?

texts:
So I have a collection with 55 million documents or so. I've enabled system profiling to check for slow queries and I do have a few every now and again.

Example:

{
        "$or" : [
                {
                        "association.ownerId" : ObjectId("55e5c40fe4b03984a0528f89")
                },
                {
                        "association.organisationId" : ObjectId("55e6d92ee4b010a9ebff510d")
                }
        ],
        "visitorId" : 384975548,
        "created" : {
                "$gte" : ISODate("2016-09-18T22:00:00Z"),
                "$lte" : ISODate("2016-09-19T22:00:00Z")
        }
}

.explain() still says it won't use indices only for this query

{
        "cursor" : "BtreeCursor visitorId_1",
        "isMultiKey" : false,
        "n" : 13,
        "nscannedObjects" : 5163,
        "nscanned" : 5163,
        "nscannedObjectsAllPlans" : 44745,
        "nscannedAllPlans" : 44993,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 1621,
        "nChunkSkips" : 0,
        "millis" : 41201,
        "indexBounds" : {
                "visitorId" : [
                        [
                                2962347,
                                2962347
                        ]
                ]
        },
        "server" : "<censored_host>:27017",
        "filterSet" : false
}

Here are some indices I have:

{
            "v" : 1,
            "key" : {
                    "association.ownerId" : 1,
                    "association.organisationId" : 1,
                    "visitorId" : 1,
                    "created" : 1
            },
            "name" : "association.ownerId_1_association.organisationId_1_visitorId_1_created_1",
        "ns" : "censored_dbname.events"
},
{
        "v" : 1,
        "key" : {
                "association.ownerId" : 1,
                "visitorId" : 1,
                "created" : 1
        },
        "name" : "association.ownerId_1_visitorId_1_created_1",
        "ns" : "censored_dbname.events",
        "background" : true
},
{
        "v" : 1,
        "key" : {
                "association.organisationId" : 1,
                "visitorId" : 1,
                "created" : 1
        },
        "name" : "association.organisationId_1_visitorId_1_created_1",
        "ns" : "censored_dbname.events",
        "background" : true
}

Anyone have a clue how I can improve this query?


texts: If you back up your transaction log every hour through a maintenance plan, why does your log file continue to grow? I would think the system would delete or shrink the log after the backup. Is this something I want to do?


texts: Easiest method to retrieve the content of BLOBs from a Sybase databse

texts:
I would like know the easiest way (no coding) to extract the content of the blobs of a database.

For example, I have a table which contains a column whose format is BLOB.
The result I would like to get is to extract the content of the BLOB into CSV files.

What is the most elegant method to do this?
Is there a Sybase function to perform this? Or an existing software?

Thanks in advance,


texts:
I posted this in StackExchange and it was mentioned that it should be moved to this exchange. So here it is:

I've been reading about SQL Index Fragmentation and how to rebuild/reorganize. I'm curious on how to write a script about it. I have a script that will generate the table with the table index and display the fragmentation in percent:

SELECT
    dbschemas.[name] as 'Schema',
    dbtables.[name] as 'Table',
    dbindexes.[name] as 'Index',
    indexstats.avg_fragmentation_in_percent,
    indexstats.page_count
FROM
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN
    sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN
    sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN
    sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
                             AND indexstats.index_id = dbindexes.index_id
WHERE
    indexstats.database_id = DB_ID()
ORDER BY
    indexstats.avg_fragmentation_in_percent desc

Can I add ALTER INDEX __NAME_OF_INDEX__ ON __NAME_OF_TABLE__ REBUILD to this script or do I have to write a new script.

I found http://sqlfool.com/2011/06/index-defrag-script-v4-1/ but am not sure on how I use it properly.

I'm new to database administration and am trying to setup a maintenance plan that will rebuild indexes when they hit a certain threshold.

Thank you in advance...Cheers!


texts:
I am used to PgAdmin III where I can simply create python functions like the following on a PostgreSQL database:

CREATE OR REPLACE FUNCTION pyHello (x integer)
RETURNS void AS $$
    if x == 1:
        plpy.notice("Hello World")
$$ LANGUAGE plpythonu VOLATILE;

And that works flawless:

Now, executing exactly the same query on the same database with the same user gives me an error in phpPgAdmin:

ERROR:  syntax error at or near "CREATE"
LINE 1: SELECT COUNT(*) AS total FROM (CREATE OR REPLACE FUNCTION py...
                                       ^

Here is a screenshot:

Now I'm wondering:

How to use language PL/PythonU on a PostgreSQL database via phpPgAdmin?
Is it even possible to use PL/PythonU through phpPgAdmin?
Why is it wrapping my query in a SELECT COUNT(*) FROM (...) AS sub query?

Like this:

SELECT COUNT(*) AS total FROM (CREATE OR REPLACE FUNCTION pyHello (x integer)
RETURNS void AS $$
    if x == 1:
        plpy.notice("Hello World")
$$ LANGUAGE plpythonu VOLATILE) AS sub

This puzzles me a little bit and I couldn't find any information on the internet. As far as I understand the language support is installed into the database, so why is this query working on one interface but not on the other?

The host is ArchLinux with the following package versions installed:

extra/python2         2.7.9-1
extra/postgresql      9.4.1-1
community/phppgadmin  5.1-3
community/pgadmin3    1.20.0-1

Any ideas?


texts:
SQL Server simple problem - ensure that only 1 batch is active at one time. Each batch has an assigned BatchID# - however that ID# might not be unique if a batch is re-run at some point. However, for a given ID# there should only ever be one "active" batch - that is only one batch with BatchComplete equal to NULL.

So the table I have is:

CREATE TABLE BatchTable (
     BatchNumber INT IDENTITY(1,1) PRIMARY KEY
     ,BatchID INT NOT NULL
     ,BatchStart DATETIME2(2) NOT NULL DEFAULT GETDATE()
     ,BatchComplete DATETIME2(2) NULL
     ,BatchLastOperation DATETIME2(2) DEFAULT GETDATE()
     BatchState INT NOT NULL
);

Then I have a constraint:

ALTER TABLE [dbo].[BatchTable]
ADD CONSTRAINT bt_OnlyOneBatchNumber UNIQUE (BatchID, BatchComplete)
GO

But last night this failed -- two inserts occurred in the same moment and both were inserted - chaos ensued.

I am guessing the problem was that ("20160619", NULL) != ("20160619", NULL) and therefore the rows were seen as unique. So the solution would be to create a non-null "IsComplete" column rather than relying upon the existence of the datetime in BatchComplete column.

So my questions are:

Is my guess correct?
Is there a better way? Surely this kind of pattern has to be pretty common (I have done something similar before though I used triggers rather than a constraint).

The BatchState might work rather than IsCompete -- but the constraint would need to be for a given BatchID all other rows must have a BatchState greater or equal to 4. Not sure how to make that constraint.


texts: What is the easiest way to move data from Oracle to SQL Server?

texts:
One of our products supports both Oracle and SQL Server as database backend.  We have a customer who wishes to switch from an Oracle backend to Microsoft SQL Server, which isn't a typical transition for us.

What is the easiest way to get all the data from the entire Oracle Schema into the SQL Server database?

The schema only contains plain old tables and nothing fancy.  There might be one or two stored procedures that we'll have no problem migrating by hand.

I could use Oracle's SQLDeveloper to export the table data as CREATE and INSERT statements, but these won't match the Syntax used on SQL Server and I am not looking forward to having to manually fix the syntax errors.


texts: select runs in batch mode, but CTAS runs in row mode

texts:
I have the same query that shows as running in batch mode when run as a select statement, but row mode when the same query is the select part of a CTAS operation. Both tables (plus the resulting table) are all distribution aligned.

Why is this? Obviously I would like it to run in batch mode if possible.

Causes row mode execution:

create table mytable1
with
(
    distribution = hash(c1)
)
as

query:

with  filterData as
(
    select
        a
        ,b
        ,c
        ,d
        ,DateStamp
    from [DW_reporting].[table1]
    where num > 0
)

    select
        p.[DateStamp]
        ,p.[a1]
        ,p.b1
        ,p.c1
        ,p.d1
        ,e.a
        ,e.b
        ,case when e.d is not null then 1 else 0 end as Flag
    from SOmeOtherTable p
    left join filterData e on e.a = p.a1
                                            and e.c = p.c1
                                            and e.DateStamp < p.DateStamp


texts: How to copy a Postgres database within the same DB cluster?

texts:
I want to replicate a Postgres database without dumping data using pg_dump. Both source and target database are on the same machine (Test and Production server).

I would like to create a master_db_copy as exact copy of the production server master_db.

I tried from the command line:

pg_dump -vO master_db | gzip > master_db
gunzip -cV master_db.sql.gz | psql master_db_copy

Is there a better / faster way?


texts: Choose record based on varying condition

texts:
I have a table that a primary key that can have one to two different status' associated with it. It looks like this:

Key Status
123 Book
123 Change
122 Book
121 Book
121 Change

If there is a record that has status of 'Change' then I only want to select that record and not the record with the same key that has a status of 'Book'.  If the key only has a status of 'Book'  then I always want to choose that record.  It is not possible to have a status of 'Change' without having a status of 'Book' first.

This is what I would like to see returned:

Key Status
123 Change
122 Book
121 Change

Any help?


texts: Configuring SQL Server management data warehouse (MDW) for data collection

texts:
I am configuring a data warehouse on an SQL Server to collect data. However, the process is not finishing and it stays as per below for hours until it is manually stopped:

Have you ever encountered this? I searched online but found no similar issues and from YouTube videos it seems that the process finishes quite quickly.

Furthermore, once this is done, can you collect data from a VM that is on a different domain from the VM where the management data warehouse DB is (no trusting exists between the two domains).


texts: If I use DBMS_Profiler to profile a long running (5 hour) PL/SQL script, how much longer should I expect the script to run with profiling?  I realize this will be dependent on what the script is doing, but in general am I looking at seconds, minutes, or perhaps more?


texts: Index for range of timestamp with varchar fields

texts:
I am using postgress database (Version 9.5.4), I have a table structure like this,

create table test (
    id serial primary key,
    company_name varchar(255) not null,
    department varchar(255) not null,
    joined_date timestamp not null
);

Following are my exact queries (Only the below three not any other),

Query 1

SELECT *
FROM test
WHERE joined_date::date BETWEEN '2017-09-25' AND '2017-10-25';

Query 2

SELECT *
FROM test
WHERE joined_date::date BETWEEN '2017-09-25' AND '2017-10-25'
  AND company_name = 'xxx';

Query 3

SELECT *
FROM test
WHERE joined_date::date BETWEEN '2017-09-25' AND '2017-10-25'
  AND company_name = 'xxx'
  AND department = 'yyy';

I want to speed up my query execution time, I tried to add index some thing like this,

CREATE INDEX index_test ON test (joined_date, company_name, department);

After creating index, the query execution time doesn't have any improvement. On adding EXPLAIN keyword, I found the index is no longer used. But when adding equals condition instead of between keyword, index is being used. But my use case would be within date range and optional fields with equal condition. How to achieve my index use case with most efficient performance?


texts: Appropriate column type(s) for dates and dynamic pricing data

texts:
What is the appropriate column(s) type(s) (trying to be database agnostic, but realize that could play into the answer) to store a months worth of pricing data (i.e. could be the daily hotel rate, etc.) as a snapshot for every day of the month?

The use case is to collect pricing data on a daily basis for a given month.  For example, on February 1st, collect 28 (2/1 - 2/28) days worth of data, then on February 2nd, collect 27 (2/2 - 2/28) days worth of data,...,on February 28 collect 1 (2/28) day worth of data.

The reason for continually collecting the pricing data is that prices often change and given the example above so that historical pricing data is recorded (as opposed to just rewriting the pricing data for a given month).


texts: How to avoid using the IN clause in sql query used in a stored procedure

texts:
Is there a better way to write the SQL other than using the IN clause in the below given SP. When i am using this IN clause, i suffer a performance dip due to large number or records involved in the User and Member tables.

CREATE PROCEDURE [dbo].[sp_MemberIdsFromUserIds] @dtUserIds UNIQUETABLE readonly
AS
  BEGIN
      SELECT userID,
             Memberid
      FROM   Member
             INNER JOIN USER
               ON UserMemberID = MemberiD
      WHERE  userID IN (SELECT UniqueId
                        FROM   @dtUserIds)
  END

Kindly suggest the alternative as i don't have a full knowledge in writing SP


texts: Accidentally imported data tables into Master database — Any cleanup needed beyond deleting?

texts:
We accidentally imported some data tables into our Master database with a CREATE TABLE and BULK INSERT script.  It was probably about 20 GB of data before we caught it.

We immediately deleted all of the tables.

What other cleanup or diagnostic tools should we run?  Should we be on the lookout for anything?


texts: Msg 6355 "Conversion of one or more characters from XML to target collation impossible" when querying sys.dm_exec_query_plan

texts:
I like to find missing indexes on the go, looking at the execution plans!
It can potentially give me an indication where further to look at if I want to improve something that is currently running.
For doing this I use the following query:
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
    er.session_id,
    er.blocking_session_id,
    er.start_time,
    er.status,
    dbName = DB_NAME(er.database_id),
    er.wait_type,
    er.wait_time,
    er.last_wait_type,
    er.granted_query_memory,
    er.reads,
    er.logical_reads,
    er.writes,
    er.row_count,
    er.total_elapsed_time,
    er.cpu_time,
    er.open_transaction_count,
    er.open_transaction_count,
    s.text,
    qp.query_plan,
    logDate = CONVERT(DATETIME,GETDATE()),
    logTime = CONVERT(DATETIME,GETDATE())
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) s
CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp
WHERE
    CONVERT(VARCHAR(MAX), qp.query_plan) LIKE '%<missing%'

It generally works fine; however, I have recently encountered a problem with collation and XML:

Error message says:
Msg 6355, Level 16, State 1, Line 40
Conversion of one or more characters from XML to target collation impossible

I have already found out what is causing it:
 -- get only the applications from Italy:
exec usp_sel_outstandingItems
                                    @startdate='2021-04-07 00:00:00',
                                    @endDate='2021-08-15 00:00:00',
                                    @statusDateStart=NULL,
                                    @statusDateEnd=NULL,
                                    @office=N'UK',
                                    @country=N'IT ',
                                    @userState=N'ParticipantPlaced',
                                    @outstandingBalance=0

My question is:
What inside my query gets upset about the collation?
What can I do to possibly get over this error?
collate hints?
select @@version

Microsoft SQL Server 2016 (SP2-CU15-GDR)
(KB4583461) - 13.0.5865.1 (X64)   Oct 31 2020 02:43:57
Copyright (c) Microsoft Corporation  Enterprise Edition:
Core-based Licensing (64-bit)
on Windows Server 2012 R2 Datacenter 6.3 <X64> (Build 9600: )
(Hypervisor)


texts: Why Percona MySQL is crashing again and again

texts:
We have Percona MySQL Cluster running with three nodes on CentOS.
Server : Percona-XtraDB-Cluster-server-56-5.6.19-25.6.824.el6.x86_64
One node is crashing repeatedly.

Thread pointer: 0x18910ba30
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f354c30fd38 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x8f36b5]
/usr/sbin/mysqld(handle_fatal_signal+0x4c4)[0x6635c4]
/lib64/libpthread.so.0(+0xf710)[0x7f3b73fde710]
/usr/sbin/mysqld(_ZN13MYSQL_BIN_LOG19raw_get_current_logEP11 st_log_info+0x2e)[0x89b1be]
/usr/sbin/mysqld[0x8a0833]
/usr/sbin/mysqld[0x59eb2c]
/usr/sbin/mysqld(_Z24plugin_foreach_with_maskP3THDPFcS0_P13s t_plugin_intPvEijS3_+0x226)[0x6f7746]
/usr/sbin/mysqld(_Z28ha_start_consistent_snapshotP3THD+0x281 )[0x5a8d51]
/usr/sbin/mysqld(_Z11trans_beginP3THDj+0x114)[0x78a7f4]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x216c)[0x6e924c]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x65 8)[0x6ed768]
/usr/sbin/mysqld[0x6ed8c1]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3 THDPcj+0x1362)[0x6ef412]
/usr/sbin/mysqld(_Z10do_commandP3THD+0x1e3)[0x6f0f73]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x17f)[0x6b9bdf]
/usr/sbin/mysqld(handle_one_connection+0x47)[0x6b9db7]
/usr/sbin/mysqld(pfs_spawn_thread+0x12a)[0xae9b4a]
/lib64/libpthread.so.0(+0x79d1)[0x7f3b73fd69d1]
/lib64/libc.so.6(clone+0x6d)[0x7f3b724ddb5d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7effb4e03890): is an invalid pointer
Connection ID (thread ID): 202009
Status: NOT_KILLED

I am unable to understand why it is crashing and what should I look at.

I have already tried following but server still crashes with same output.
1. Removed Percona Server.
2. Removed all data under /var/lib/mysql
3. Rebooted server
4. Install Percona Server
5. Sync with cluster (full SST) - Copies all data from running node.

It still crashes with same error.
It does not crash immediately but randomly after about 18-22 hours.


texts:
I need to run a query against a database and group the records by different date ranges. The data type of the date field is varchar and in the format dd-MMM-yyyy e.g. 19-Aug-2016.

The date ranges are as follow: 13–18, 19–35, 36–55 and >55. This range is determined by subtracting the current date from the date stored in the field.
e.g. if current date is 19-Aug-2016 and date stored in the database field is 18-Aug-2003, the year difference will be 13. So all the date differences between 13 and 18 will be grouped together, all date differences between 19 and 35 will be grouped together etc.


texts:
Our SQL Server instance has the following settings:

Database default locations:

Data: D:\
Log: E:\

When creating a new database with defaults (create database test) it respects these settings.

When we create a new SSISDB catalog, using these steps https://technet.microsoft.com/en-us/library/gg471509(v=sql.110).aspx (through SSMS or PowerShell) it appears to ignore the log file setting, and put both the data file and log file in D:.

Is there a way to create this catalog with the settings being respected, or do we need to make sure to re-arrange everything once we've already created the catalog?


texts: "can't return a result set in the given context" error when calling stored procedure - differences between mysql 5.0 and 5.5?

texts:
I have a stored procedure to regenerate leafs (nleft, nright) in simple (id, id_parent) tree. (sample table)

When I'am calling

CALL EdgeListToNestedSet( 'category', 'id_category', 'id_parent');

it works great with version 5.5.29 of mysql but on 5.0.26  I've got an error:

#1312 - PROCEDURE test.EdgeListToNestedSet can't return a result set in the given context

Any ideas what can be wrong and how to make it working?


texts: Find statements related to sys.dm_db_missing_index... tables (SQL Server)

texts:
I have read up different articles related to SQL Server 2008 on how to find out missing indexes using the tables sys.dm_db_missing_index_groups, sys.dm_db_missing_index_group_stats etc.

One thing I'd like to know: Is it possible to extract the queries that would benefit from these indexes?

Thanks all!


texts: Using data from other database to populate a new database

texts:
I have a SQL Server database DB1 and want to move data from DB1 to DB2. But only want some of the data to be moved.

I want all the rows from DB1 that have a catParent that is the same as I Id from DB2. And I only want the DB1.catDescription = DB2.Name and DB1.catLink = DB2.URL

How do I manage that? Do I need a sub select query inside my insert query and how is the syntax for this?

DB1

CREATE TABLE [dbo].[category](
    [catID] [bigint] IDENTITY(1,1) NOT NULL,
    [catDescription] [varchar](50) NOT NULL,
    [catParent] [bigint] NULL,
    [catSortOrder] [bigint] NOT NULL,
    [catLink] [varchar](255) NOT NULL,
    [catShow] [tinyint] NULL,
    [catTarget] [varchar](3) NOT NULL,
    [catAssign] [varchar](3) NOT NULL,
 CONSTRAINT [primary key] PRIMARY KEY CLUSTERED
(
    [catID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

DB2

CREATE TABLE [dbo].[Categories](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Guid] [uniqueidentifier] NOT NULL,
    [Name] [nvarchar](max) NOT NULL,
    [Description] [ntext] NULL,
    [AssignArticles] [bit] NULL,
    [URL] [nvarchar](max) NULL,
    [Target] [int] NOT NULL,
    [Publish] [bit] NOT NULL,
    [ParentCategoryId] [int] NOT NULL,
    [Sort] [int] NOT NULL,
    [RequireLogin] [bit] NOT NULL,
    [LevelVal] [int] NULL,
    [PageLayoutId] [int] NOT NULL,
    [M03PictureId] [int] NOT NULL,
    [Sitemap] [bit] NOT NULL,
    [Search] [bit] NOT NULL,
    [LinkText] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


texts: Drop Oracle data file with 0 bytes and in offline (needs recovery) state

texts: What can I do to drop a data file mistakenly created and currently with 0 byte and in offline (needs recovery) state?


texts: Authenticate mssql server using kerberos all running on local mac os

texts:
I have SQL Server running on docker and did the setup using the following :
https://www.quackit.com/sql_server/mac/install_sql_server_on_a_mac.cfm
Then I setup Kerberos on local using the following :
https://www.thetechnojournals.com/2019/12/setting-up-kerberos-in-mac-os-x.html
My DB client is DBEAVER - I can authenticate using the normal authentication and access the sql server, now I want to authenticate using kerberos how do I do that?
any help is highly appreciated


texts: How to check if logs has 7 consecutive days?


texts: MYSQL need log executed query in table along with executed user name

texts: I would to like the executed quires with entire database tables for some mysql users only. Because some of users executed wrong query in production environment. And needs to store this log in new table. Is there any possible to do this? Thanks in advance.


texts: Both replication masters stop by themselves

texts:
I did a master/master replication with MySQL on a Gentoo OVH Release 2, all work fine in my test phase. I put it in production, the replication works fine for 1 or 2 days, but this morning, I don't know why, my slave stops running!

So now the log position is bad. I can't just restart slave and my replication doesn't work.

I want a master/master replication to make a backup server with an IP failover, so only one server is writing/reading in the database at same time.

When I go to MySQL and click on Show slave status, I see an error like:

  Error 'Duplicate entry '411465' for key 1' on query. Default database .... etc.

Did this error stop my replication? If yes, why do I get this error? The second server does nothing on the database, so normally, there is no problem with the autoincrement, right?


texts: Setting up MySQL master-slave replication


texts: backup of databases - consistently back up databases with control of log truncates

texts:
My question is how can I back up my database by controlling the log truncates?

I need to perform a testing with VEBF (Veeam Endpoint Backup FREE). A test case that I have to make now is that I have to back up Windows Server 2012 R2 as well as SQL Server 2014 with this software, ie the databases and the requirement to do so is to control the log truncates. The database I am using already has the Recovery Model "Simple".


texts:
Is there a way to create an index in MySQL if it does not exist?

MySQL does not support the obvious format:

CREATE INDEX IF NOT EXISTS index_name ON table(column)
ERROR 1064 (42000): You have an error in your SQL syntax;...

MySQL version (mysql -V) is 5.1.48, but I think that MySQL lacks the CREATE INDEX IF NOT EXIST ability in all of its versions.

What's the right way to create an index only if it does not already exist in MySQL?


texts: Difference in efficiency between NVL and IS NULL

texts:
I have the below query (showing the last part of the query). In the first example I get a result (which is not exactly as desired) but the results arrive within 10min. The second version results in an error: snapshot too old: rollback segment number.

Basically I'm joining in a second table with one key, and add a second key if available, and a third key if available. If key #2 and #3 are null then the join should only happen on the first key.

My research has shown that the snapshot too old error message originates from the fact that the query takes too much resources. That's why I would like to understand in how far NVL is more efficient than IS NULL and what I can do to adjust my query.

quick but result is not as desired:

..…….
   left outer join tlea
          on (    dset_cob_dt = tlea_cob_dt
              and meas_data.tlsn_trd_id = tlea.tlsn_trd_id
              and nvl (meas_data.tlsn_vrsn_num, -1) =
                     nvl (tlea.tlsn_vrsn_num, -1)
              and meas_data.tlsn_leg_id = tlea.tlsn_leg_id);

works correctly but getting errors:

    ………
             LEFT OUTER JOIN tlea
       ON (dset_cob_dt = tlea_cob_dt
        AND  meas_data.tlsn_trd_id = tlea.tlsn_trd_id
        AND (meas_data.tlsn_leg_id = tlea.tlsn_leg_id OR tlea.tlsn_leg_id IS NULL)
        AND (meas_data.tlsn_vrsn_num = tlea.tlsn_vrsn_num OR tlea.tlsn_vrsn_num IS NULL)
         );

ORA-12801: error signaled in parallel query server P034
ORA-01555: snapshot too old: rollback segment number 240 with name "_SYSSMU240_2257379025$" too small

How can I make the second quefry more efficient so I can avoid the error? Is there a way to achieve the same with NVL?

UPDATE:
The query should always join on tlsn_trd_id and if available (not null) additionally on tlsn_leg_id and if available (not null) additional on tlsn_vrsn_num as well


texts: How to view the original script that built a materialized view and that was created by another user?

texts:
In the Oracle database server use, another user (called MIMIC2DEVEL) created a materialized view and made it public. How to view the original script that was used to built it?

Here are the grants on the materialized view:

I am aware of How to view the original script of a materialized view? but the two solutions proposed there do not seem to work for materialized views that was created by another user.

I use Oracle SQL Developer and the database server runs:


texts: Troubleshooting Oracle ORA-08103 - object no longer exists

texts:
I am getting this error from Oracle intermittently. A trigger is truncating a partition from a partitioned table while a long-running query is running against  it.

Since I'm truncating a different partition than the ones I'm intending to query, I expect this to work. I suspect a rogue query plan, doing PARTITION LIST (ALL) where I want (ITERATOR) or (JOIN-FILTER).

The query is complex, joining multiple partitioned tables.

So my question is: can I get any more detail out of Oracle to figure out precisely which table (or index) in the query is causing the problem? And is there any way I can capture the query plan that was in effect when the error occurred? (which could be different than the plan if I look at it right now)


texts: MySQL Innodb: What is the difference between running 'mysqlcheck --optimize --all-database' compared to running 'optimize table' in the mysql client?

texts:
Without having to script an 'optimize table' query to do all the databases/tables, we would like to run mysqlcheck --optimize --all-database instead.

What are the drawbacks of doing this through mysqlcheck; both would defragment the tables, correct?


texts: Oracle 11g, Will droping partition reuse free space on bigfile tablespace

texts:
I have four range partitioned tables which belongs to bigfile tablespace. Also local indexes on that tables belongs to other bigfile tablespace.
My partitioning range is one day and I have stored procedure that drops partitions older than 30 days fro each table.
As for now (15 days from start of gathering data) my datafiles (both data and index) are growing steadily as expected.
My question is when stored procedure drops first partition older than 30 days will that space be avaliable to new partitions or datafile wil continue to grow?


texts: How to design MySql database design for Jira-like application?

texts:
I am new to software development and developing one application for learning new skills, I am trying to implement some of the basic features of Jira software. I am stuck at database design, I have tried some of the design but I am not sure if that is the correct way to do. Here is the first database design. and here is the second.
My application's workflow will be New user -> New Project -> Set Backlogs (Tasks and Bugs) -> Board that includes Task and Bugs (To do, In process, Done). Data of TaskName and BugName from Backlog table will eventually be transfer to Board table either in the field of ToDo, InProcess or Done.
My question is How to design database that will not double the data or this the right way to design database?. Please suggest some of the ideas of database design or any resources that might helpful for design database for my application.


texts: Splitting a csv bulk import to postgres into smaller blocks using psycopg2

texts:
I have a number of csv-files that I would like to import into my postgres-database. Since each file is quite large (200m-2bn records/file) I believe it would be better to add each file in smaller batches.

FOr comparison, when using MS-SQL and using bcp, files get automatically split into 1000 record batches. That is exactly what I (think I) want to do with COPY.

My main script is Python 3.5.2, the database is PostgreSQL 9.5.10 on Ubuntu 16.04 LTS. I am using psycopg2's copy functionality (on the simple account of it being the first that worked). I'm happy to change that, as long as I can call it from python (since I need to do some other processing per file -- organizing files, searching, determining the target stage table, unzip, cleanup, etc. -- since I am not sure how exactly the data transfer works using this method. Per psycopg2's doc the sql-version of copy is used (https://www.postgresql.org/docs/current/static/sql-copy.html), not the command line version. However I suspect the entire file will be buffered through stdin, but again, I'm not sure.

con = psycopg2.connect(database=dbname, user=user, password=password, host=host, port=port)
cur = con.cursor()
f = open(pathTempFile)
cur.copy_expert("""copy public.stage_datafile from STDIN DELIMITER ',' CSV HEADER""",
    file=f)
f.close()
con.commit()
con.close()

This process works fine, and I'm not even sure there are any drawbacks. However, it seems there might be a better/more robust way? I will use a trigger to export the data from the staging tables into the normalized main tables, surely that would benefit from smaller data blocks?

I appreciate any suggestions on making this process more robust.

Addendum:

I only need to import the data once, not regularly.
I could also use sed to generate some temporary files and import those, in fact I'm doing that while debugging


texts: Why does sp_whoisactive show locks on databases, but not tables?

texts:
Recently I have noticed some threads running on our sql server which are invoked from our application, which are in a sleeping status for a few minutes and have open transactions (usually just one).

I added the parameter @get_locks = 1 to sp_whoisactive to see what they were holding and the xml result set looks like this

<Database name="NOG_APP">
   <Locks>
      <Lock request_mode="S" request_status="GRANT" request_count="1" />
   </Locks>
</Database>

I don't see any objects in here... how am I to interpret this? Does it mean that this thread is not holding any locks? If so why doesn't the output return NULL?

Running SQL Server 2012 SP3.


texts: Database Design - Two sources of input, one for creation and the other for verification

texts:
This might be a rather strange request but I receive two (slightly varied) versions of an object via two sources.
Source A:
int_field_1, double_field, datetime, ref_str_1, ref_str_2, unique_ref_str_1
Source B:
int_field_1, double_field, datetime, ref_str_1, ref_str_2
I would like to use one source as verification of the data integrity of the other source (i.e. both sources should provide the same values for all fields received).
Since Source A has more fields than Source B, it would seem natural to populate the data with source A first, then cross checking it with source B. Unfortunately, due to transmission routes, there is a possibility that source B arrives before source A.
Both sources receive data only once, and only in real time, there is no mechanism for retrieving data later on or introducing a delay. Therefore, received data must immediately be stored.
What is the suggested database design for such purpose?
I have thought of two options:

SourceA and SourceB have their own tables with the exact same fields and a joining table with a single boolean value to indicate if both sources provided the same data.
A single table with all fields + source field (A or B) that stores 2 rows per object + a joining table with a single boolean value to indicate if both sources provided the same data.
A single table with all fields + two boolean fields to indicate if A/B values were exactly the same as that stored in the table.

If there are any better suggestions, I am open to them.
Thanks!


texts: Connection to Oracle DB 19c fails when listener is set to the host IP address

texts:
I am new to Oracle DB and need help in solving the following issue.
I am using sqlplus (SQL*Plus: Version 21.1.0.0.0) to connect to the Oracle DB 19c.
Statement: I am able to connect to the Oracle DB from the host (192.168.0.109), VM running on top of the host (192.168.0.108), and also from other system within the same LAN (192.168.0.106) when I change the IP to 0.0.0.0 from 'localhost' in the listener.ora file as below:
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

Issue faced: Cannot connect from the VM (192.168.0.108) or from the other systems within the LAN (192.168.0.106) when I change the IP to the host IP (192.168.0.109) in the listener.ora file. Please find the modified listener.ora below:
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.109)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

Error Details: The error reported by sqlplus tool is as follows:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Now, let me share the content of the tnsnames.ora file too (if required):
LISTENER_POLA =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

POLA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pola)
    )
  )

Some more details:

The host on which the Oracle DB is residing (192.168.0.109) is reachable from all the other systems including the VM
telnet 192.168.0.109 1521 from all the systems is showing it's connected.
The VM is running on Oracle Virtual Box 6.1 and is configured in Bridge mode.
The Oracle DB 19c and the VM is running on top of Windows Server 2016.

Query:
What configuration do I need to do so that sqlplus can connect to the Oracle DB from any system within the same LAN when the listener.ora file is configured with the IP address of the host system (192.168.0.109)?
Output of LSNRCTL status:
LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 16-MAR-2021 23:18:31

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.109)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production
Start Date                16-MAR-2021 23:16:07
Uptime                    0 days 0 hr. 2 min. 23 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\Polarbear\WINDOWS.X64_193000_db_home\network\admin\listener.ora
Listener Log File         C:\Polarbear\diag\tnslsnr\WIN-4SO5O8T8UJ6\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.109)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Output of the command sqlplus / as sysdba:
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 17 00:00:29 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select count(*) from dba_tables;

  COUNT(*)
----------
      2182


texts:
I am new to SqlCmd. Can anyone give a short explanation of the following command?

SQLCMD -S VMLIVE-DATA\SQLSERVER -Q
"RESTORE DATABASE GIS_ENG FROM DISK = N'\\10.143.251.101\ClientData\Backup\DatabaseBackups\DB_ENG.bak'
WITH  FILE = 1,
MOVE N'DB_ENG' TO N'\\10.153.201.11\E$\SQLSERVER\Data\DB_ENG.mdf',
MOVE N'DB_ENG_log' TO N'\\10.153.201.11\E$\SQLSERVER\Data\DB_ENG.ldf',
NOUNLOAD,  REPLACE,  STATS = 10"


texts: SSMS 2012 mutiple query windows using same session

texts:
I wonder if it is possible to connect multiple files or query windows in SSMS to one session? The reason is that I want to use local temporary tables that are created in one file/query-window in other file/query-windows. As local temporary tables have session scope this seems to be the only way to achieve this.

We use the temp tables for separating session data in our application. I use SSMS for development and in the development context it would be useful to have the same tmp tables available in multiple SSMS windows.

What i do now is copy the creation code of the tmp tables in all query windows, then evaluate that part and then cut them out again. So they are avaible in that session, but that's a bit cumbersome. So basically it is an SSMS question and not a SQL Server question.


texts: Extract filename without extension from the absolute location

texts:
I'm trying to get a filename contained in the value of a specific column of my table. My table looks like this:

absolutel_path
\\Path\filename.extension

I need to extract the filename (in the above filename) from the absolute_path (\\Path\filename.extension). Which function should I use to get my filename (substring) out?


texts: Size of a MyISAM table doubles after OPTIMIZE

texts:
I have this table:

CREATE TABLE `foo` (
  `CalculatedResultsId` int NOT NULL,
  `Md5Hash` char(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `SectionData` json NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

ALTER TABLE `foo`
  ADD UNIQUE KEY `CalculatedResultsId` (`CalculatedResultsId`),
  ADD UNIQUE KEY `Md5Hash` (`Md5Hash`);

which contains ~400k rows and is ~800MB big. This is the output from PMA:

Then I delete 2'454 rows [< 0.6%], which produces an overhead of around 5MB which is also documented in PMA:

Running OPTIMIZE Table suddenly doubles the size of this table. I checked the real size of the table file in the directory and this is correct. It really doubled!

My question:

Why, what is going on? What happened, what is the reason, that the table size doubled?

The only way I found how to reduce the size of the table as it was before is to create a new table and re-fill it. Another very strange fact:

Running this command in order to get the copy of the big table CREATE TABLE foo_new AS SELECT * FROM foo; runs for 27! minutes for these 400k rows.

Creating an empty table and then copying with INSERT INTO foo_new SELECT * FROM foo needs 24! seconds.

Why such a difference? I don't know, but somehow it looks to me like a very serious design problem in MySQL, or a bug.

MySQL 8.0.20 running on SLES 15.1


texts: Long running VACUUM VERBSE in Postgres 9.5 - how much time remaining?

texts:
I am vacuuming a very large (718GB) table containing a lot of dead tuples and 4 indexes.

VACUUM ANALYSE VERBOSE bigtable;

This vacuum has been running for over 11 hours (!) now and I am trying to estimate the remaining time for it to complete, or, at least understand in which "phase" the vacuum process is: scanning heap? cleaning indexes? vacuuming heap? I am referring to the phases described in Vacuum Progress Reporting, introduced in 9.6.

Since I am running Postgres 9.5, I do not have access to this nifty feature. I am trying to make sense of the "verbose" vacuum messages, which look like this:

INFO:  scanned index "bigtable_timestamp" to remove 11184548 row versions
DETAIL:  CPU 77.05s/336.97u sec elapsed 745.75 sec
INFO:  scanned index "bigtable_trip_id" to remove 11184548 row versions
DETAIL:  CPU 87.86s/371.96u sec elapsed 846.21 sec
INFO:  "bigtable": removed 11184548 row versions in 157984 pages
DETAIL:  CPU 3.50s/1.93u sec elapsed 22.42 sec
INFO:  scanned index "bigtable_pkey" to remove 11184526 row versions
DETAIL:  CPU 72.27s/330.03u sec elapsed 704.25 sec
INFO:  scanned index "bigtable_step_id" to remove 11184526 row versions
DETAIL:  CPU 90.21s/370.86u sec elapsed 850.30 sec

The above sequence of messages has repeated itself for the past 11 hours, with no end in sight.

Is there any way to tell, based on those messages, in which phase the vaccum is now?

I've also tried to look at the bytes-read of the vacuum process on Linux, as suggested in this answer, and compare it to the total_bytes of the table and indexes, but this didn't work for me. The process has already read 5.1TB (5101054656512) from the disk although the table including all indexes is estimated at "only" 718GB (pg_class.total_bytes).


texts: I have a query on two big tables that is too slow!

texts:
I have a simple query:

SELECT  t1."TimeS" as TimeS, t1."JobID" as JobID, t1."TaskIndex" as TaskIndex,  t1."MachineID" as MachineID ,t2."EventType" as EventType,  t1."UserName" as UserName, t1."CpuReq" as CpuReq, t1."MemReq" as MemReq,
  t2."TimeS" as FinTime  , (t2."TimeS"-t1."TimeS") as duration
FROM  scheduled_tasks_table  as t1 ,  public.dead_tasks_table as t2
     WHERE t1. "TaskIndex"= t2. "TaskIndex"
     AND t1."JobID"= t2."JobID" AND (t2."TimeS"-t1."TimeS")>='0'

I have 15GB memory (RAM) and my Config File is:

max_connections = 200
shared_buffers = 3840MB
effective_cache_size = 11520MB
work_mem = 19660kB
maintenance_work_mem = 960MB
min_wal_size = 1GB
max_wal_size = 2GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100

Both my tables both have a single primary key and a single indexe as:( TaskIndex, JobId, Timet)

But this query is really slow:

EXPLAIN:

" "Merge Join  (cost=19982628.08..1436656715.61 rows=20251904912 width=105)"
"  Merge Cond: ((t1."TaskIndex" = t2."TaskIndex") AND (t1."JobID" = t2."JobID"))"
"  Join Filter: ((t2."TimeS" - t1."TimeS") >= '0'::bigint)"
"  ->  Sort  (cost=12319742.87..12438008.64 rows=47306308 width=93)"
" Sort Key: t1."TaskIndex", t1."JobID""
"  ->  Seq Scan on scheduled_tasks_table t1  (cost=0.00..1438498.08 rows=47306308 width=93)"
"  ->  Materialize  (cost=7662858.23..7874671.39 rows=42362632 width=28)"


texts: MongoDB MMAPv1 vs WiredTiger storage engines

texts:
In mongoDB3 appeared a new storage engine: WiredTiger. Yet, MMAPv1 is still the default choice in Mongo.

One might not be better than the other, it's often a matter of use case and choosing the right tool for the job. But which engine is right for what job?

In fact, while MMAPv1 is the default engine, WiredTiger seems better in almost every field. It has the same features as MMAPv1 plus:

better write performance,
document level concurrency,
compression,
snapshots and checkpoints system.

I found a comparing table on MongoDB's blog:

So except if you are on Solaris, is there a reason not to choose WiredTiger?

EDIT

Here are two videos that explain in details the internals of
WiredTiger and MMAPv1.


texts: ORACLE: performance of query running across two schemas

texts:
We have a current production schema that stores all of our data. We're in the process of trying to integrate a different system to our system. They (vendor from 2nd system) proposed creating a 2nd schema on the same database that hosts our production schema. In that 2nd schema, they are going to migrate the data from 2nd system over and try to transform data so it's similar to our production system. Nothing changes in our production schema.

The plan is to create some reports that will query both the production schema as well as the tables from the 2nd schema (that contains transformed data from the other system). The report is going to be running from our original production system.

Here's one scenario:

Table STUDY is in both production schema (Schema A) and the 2nd schema (schema B). STUDY table in schema A contains live production data from our current system. STUDY table in schema B contains different data that were migrated from the other system. We create a report that should look into both STUDY table in schema A as well as Schema B.
Similar to scenario 1, but this time we're trying to join the STUDY table in both schema A & schema B. Generate a total STUDY report with data from both schemas.

Assuming STUDY table contains thousands of rows and its the header table, and there is multiple child / detail tables that links to STUDY table. Both schemas resides on the same oracle database instance, storage on the same server pretty much share the same infrastructure.

Questions I have:
- What is the performance hit / negative impact on our current production system (schema A)? should we expect a big performance hit if we're going about our business like normally (before having the 2nd schema)

What is the pros and cons of this approach? (don't touch the current production schema, put data from 2nd system into a 2nd schema)
What are some of the other things we have to consider when we plan to go with this approach? do we need to increase the memory used by Oracle, increase share pool, SGA etc?
What are the ongoing maintenance issues we could have for the Oracle database (with the addition of the 2nd schema)? We have oracle replication to DR database to sync a dark database every 10 minutes for current production system.
Any other downsides you can think of? (as I'm technical but certainly not a DBA or an oracle expert)


texts: AddWithValue performance and plan cache implications

texts:
Recently I noticed my company's application using AddWithValueto pass parameter values to dynamic, parameterized queries. Example:

cmd.Parameters.AddWithValue("@VehicleId", Vehicles.VehicleId);

In the database, the data type for VehicleID is INT.

As I understand it, because AddWithValue (which is deprecated) does not specify the data type/length, this '@VehicleID' is converted and potentially converted incorrectly. Does that conversion affect SQL Server performance in the case of an 'INT'?

Would this then cause issues beyond plan cache pollution? Is there a performance hit caused by the conversion?


texts: SQL Server user cannot select from a table it just created?

texts:
I'm having trouble understanding permissions in SQL Server (in this case SQL Server 2012, but not sure how version-dependent this issue is). I can create a database, create a login/user on master, create a user for that login on the new database, grant 'CREATE TABLE' and 'ALTER' on the [dbo] schema in that new database, and then (as the new user) create the table but not select from it.

Is there some different permission necessary to SELECT from tables I just created? The end game would be to be able to GRANT various permissions on that table to [public] but if I can't SELECT from it then I'd imagine I can't do anything else on it? (Code follows)

--logged in as 'sa'
    USE [master]
    GO

    CREATE DATABASE [ThisTestDB]

    CREATE LOGIN [TestAcct]
        WITH PASSWORD = 'Passw0rd',
        DEFAULT_DATABASE = [ThisTestDB]

    CREATE USER [TestAcct]
        FOR LOGIN [TestAcct]
    GO

    USE [ThisTestDB]
    GO

    CREATE USER [TestAcct]
        FOR LOGIN [TestAcct]
    GO
--executed all lines above: Command(s) completed successfully

--still logged in as 'sa'
    USE [ThisTestDB]
    GO

    GRANT CREATE TABLE TO [TestAcct]
    GO

    GRANT ALTER ON SCHEMA :: [dbo] TO [TestAcct]
    GO
--execute the above lines: Command(s) completed successfully

--now, logged in as 'TestAcct'
    USE [ThisTestDB]
    GO

    CREATE TABLE tThisTable (
        id      INT IDENTITY(1,1),
        ts      DATETIME,
        Data    VARCHAR(1000))
--execute the above lines: Command(s) completed successfully

--still logged in as 'TestAcct'
    SELECT  *
    FROM    tThisTable
--executed the above, error thrown:
/*
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'tThisTable', database 'ThisTestDB', schema 'dbo'.
*/


texts: EXISTS (SELECT 1 ...) vs EXISTS (SELECT * ...) One or the other?

texts:
Whenever I need to check for the existence of some row in a table, I tend to write always a condition like:

SELECT a, b, c
  FROM a_table
 WHERE EXISTS
       (SELECT *  -- This is what I normally write
          FROM another_table
         WHERE another_table.b = a_table.b
       )

Some other people write it like:

SELECT a, b, c
  FROM a_table
 WHERE EXISTS
       (SELECT 1   --- This nice '1' is what I have seen other people use
          FROM another_table
         WHERE another_table.b = a_table.b
       )

When the condition is NOT EXISTS instead of EXISTS: In some occasions, I might write it with a LEFT JOIN and an extra condition (sometimes called an antijoin):

SELECT a, b, c
  FROM a_table
       LEFT JOIN another_table ON another_table.b = a_table.b
 WHERE another_table.primary_key IS NULL

I try to avoid it because I think the meaning is less clear, specially when what is your primary_key is not that obvious, or when your primary key or your join condition is multi-column (and you can easily forget one of the columns). However, sometimes you maintain code written by somebody else... and it is just there.

Is there any difference (other than style) to use SELECT 1 instead of SELECT *?
Is there any corner case where it does not behave the same way?
Although what I wrote is (AFAIK) standard SQL: Is there such a difference for different databases / older versions?
Is there any advantage on explicity writing an antijoin?
Do contemporary planners/optimizers treat it differently from the NOT EXISTS clause?


texts: Postgres: Get nextval in sequence without actually incrementing sequence?

texts:
It looks like select nextval('table_name') actually does the value increment.

My goal is to "predict" the nextval value on all tables in the database without actually making any incrementation. This should be a read-only operation.

I cannot run the select nextval on a read-only connection since it's actually trying to make a transaction. I would like to be able to query this and monitor the sequences on a read-only replica database.

How would you tackle this and meet the goal?


texts:
I created 12 partitions from July 2017 through July 2018 and didn't leave an empty partition at the end. Hence, am having a hard time adding new partitions using the split function. I even tried switching the oldest partition to a staging table but "split range" is taking a very long time to execute. The goal of this project was to reuse the old partitions by archiving oldest partition to another table but I am not able to use the switch function as it's taking a very long time.
Please advise so I can fix this issue.
Here are the scripts:

CREATE PARTITION FUNCTION APITPartFunc AS RANGE LEFT
FOR
VALUES (
    '2018-01-31T00:00:00'
    ,N'2018-02-28T00:00:00'
    ,N'2018-03-31T00:00:00'
    ,N'2018-04-30T00:00:00'
    ,N'2018-05-31T00:00:00'
    ,N'2018-06-30T00:00:00'
    ,N'2018-07-31T00:00:00'
    ,N'2018-08-31T00:00:00'
    ,N'2018-09-30T00:00:00'
    ,N'2018-10-31T00:00:00'
    ,N'2018-11-30T00:00:00'
    ,N'2018-12-31T00:00:00'
    )

CREATE PARTITION SCHEME [APITScheme] AS PARTITION [APITPartFunc] TO (
    [ApiTPartitions]
    ,[ApiTPartitions]
    ,[ApiTPartitions]
    ,[ApiTPartitions]
    ,[ApiTPartitions]
    ,[ApiTPartitions]
    ,[ApiTPartitions]
    ,[ApiTPartitions]
    ,[ApiTPartitions]
    ,[ApiTPartitions]
    ,[ApiTPartitions]
    ,[ApiTPartitions]
    ,[ApiTPartitions]
    )

   CREATE TABLE [dbo].[test] (
    [id] [bigint] IDENTITY(1, 1) NOT NULL
    ,[user_id] [uniqueidentifier] NOT NULL
    ,[client_id] [smallint] NOT NULL
    ,[api_tracking_type_id] [smallint] NOT NULL
    ,[dt] [datetime] NOT NULL
    ,[event_data] VARCHAR NULL
    ,[entity_id] VARCHAR NULL
    ) ON [APITPartScheme] (dt)
GO

ALTER TABLE [dbo].[test] ADD CONSTRAINT [PK_test_id_dt] PRIMARY KEY CLUSTERED (
    [id] ASC
    ,[dt]
    )
    WITH (
            PAD_INDEX = OFF
            ,STATISTICS_NORECOMPUTE = OFF
            ,IGNORE_DUP_KEY = OFF
            ,ALLOW_ROW_LOCKS = ON
            ,ALLOW_PAGE_LOCKS = ON
            ) ON APITPartScheme

--I was trying to add an empty partition by
ALTER TABLE test switch PARTITION 13 TO teststaging PARTITION 13

ALTER PARTITION scheme apitpartscheme NEXT used apitpart13

ALTER PARTITION FUNCTION apitpartfunction () spit range ('2018-08-04')

ALTER TABLE teststaging switch PARTITION 13 TO test PARTITION 13
--this would be after split works but the above script took forever so I stopped it.


texts: How to find which query is causing tempdb growing?

texts:
Good day.
I'm trying to discover which query is causing tempdb growing too much (around 100gbyte).
In my new company, people is using a lot of dynamic Excel files with various query.
I'm pretty sure that one of them is "guilty" for tempdb growing.
The only solution I've discovered is to restart sql server service (it delete and recreate tempdb).
Is there a way to discover which query is allocating so much space ?
Thanks


texts: Postgres Case-Insensitive search for Jsonb column tag keys

texts:
I have a requirement to obtain the rows which match given tags key and value case-insensitively.
Here: Key search should be case insensitive and values may be String or Array of String.
Right Now, I am using following Query :
Database : Postgres
select * from my_table_name where jsonb_contains(lower(to_jsonb(jsonb_extract_path(tags,'key1'))::TEXT)::jsonb, to_jsonb('"value1"'::jsonb))
But it is searching key as case sensitive manner.
For Example:
above query should return all records having key is (key1,Key1,KEY1) and value is 'Value1'
Can some one help me on this?


texts: Deny INSERT/UPDATE/DELETE to a particular schema

texts:
Is always hard to me to manage roles and permissions with users connecting to SQL server with Windows authentication.

There are some users (not actually SQL users on SQL, are coming from active directory) connecting to a server with SQL 2014, they are all using windows authentication, I want them to avoid doing INSERT/UPDATE/DELETE in all tables with a particular schema but they can run stored procedures that can affect the tables on that schema.

I was thinking to create a server role and then apply that server role to each database and also for new databases created.

My idea is to create a server role, assign those users to that role and then assign the role to the different databases on the server.

Is that the right way to do it?


texts:
I'm writing a script that will create a new folder and backup all databases into that folder. The script will generate the new folder, but will not save the backups into that folder. I'm not sure what I'm missing. Thank you in advance.

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT)

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

SET @path = '\\noc2-storage\IT_Backup\Weekly_SQL\CDADB1\' + @fileDate;

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @path)
EXEC master.dbo.xp_create_subdir @path
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
       BACKUP DATABASE @name TO DISK = @filename

       FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

Thank you in advance


texts: Specify cleanup by file age - PostgreSQL 9.2

texts:
I want to keep my wal_files for 30 days, using the pg_archivecleanup.

I've followed these scripts:

https://stackoverflow.com/questions/31346606/postgresql-automatic-archive-purge-not-occuring

and

https://stackoverflow.com/questions/16943599/how-to-specify-cleanup-by-file-age-or-date-with-pg-archivecleanup ...

#!/bin/bash

ARCHIVEDIR='/var/lib/pgsql/wal_archive'
CHECKPOINT=$(find $ARCHIVEDIR -type f -mtime +30 -type f -printf '%f\n' | sort -r | head -1)
cd $ARCHIVEDIR
/usr/bin/pg_archivecleanup $ARCHIVEDIR $CHECKPOINT

find $ARCHIVEDIR -type f -mtime +30 -a -type f -a ! -newer $CHECKPOINT -delete

... but it's not working for me.

I'm getting this error:

pg_archivecleanup: invalid filename input
Try "pg_archivecleanup --help" for more information.
find: `-delete': No such file or directory

my recovery.conf:

archive_cleanup_command = 'pg_archivecleaup_mv.bash -d'

Is there any way to make this work?

UPDATE 1:

I've done some tests...

recovery.conf:

archive_cleanup_command = 'pg_archivecleaup_mv.bash'

pg_archivecleaup_mv.bash:

declare -r -x PATH='/usr/local/bin:/usr/bin:/bin';

ARCHIVEDIR='/var/lib/pgsql/wal_archive'
CHECKPOINT=$(find $ARCHIVEDIR -type f -mmin +3 -type f -printf '%f\n' | sort -r | head -1)
cd $ARCHIVEDIR
exec "pg_archivecleanup" "$ARCHIVEDIR" "$CHECKPOINT" "%r";
find $ARCHIVEDIR -type f -mmin +3 -a -type f -a ! -newer $CHECKPOINT -delete

-mmin +3 for testing purpose...

By doing that, the error changes:

pg_archivecleanup: too many parameters
Try "pg_archivecleanup --help" for more information.
archive_cleanup_command "/var/lib/pgsql/bin/pg_archivecleaup_mv.bash": return code 512

UPDATE 2: Following @Sebastian Webber idea, I changed the pg_archivecleaup_mv.bash script; I'm getting the same issue that I had using the script I wrote:

#!/bin/bash -x

ARCHIVEDIR='/var/lib/pgsql/wal_archive'
LAST_BACKUP=$(ls -lto ${ARCHIVEDIR})

FILE_LIST=$(/usr/pgsql-9.2/bin/pg_archivecleanup -n "${ARCHIVEDIR}" "${LAST_BACKUP}")

## TEST FIRST
/usr/pgsql-9.2/bin/pg_archivecleanup -n "${ARCHIVEDIR}" "${LAST_BACKUP}" | find -type f -mmin +30 | xargs ls -lh

##THEN UNCOMMENT THIS LINE
#/usr/pgsql-9.2/bin/pg_archivecleanup -n "${ARCHIVEDIR}" "${LAST_BACKUP}" | find -type f -mmin +30 -delete

recovery.conf:

archive_cleanup_command = 'exec /var/lib/pgsql/bin/pg_archivecleaup_mv.bash -d "%r"'

With or without the "%r" I get the error: pg_archivecleanup: invalid filename input
Without the "%r", I put the %r into the script: /usr/pgsql-9.2/bin/pg_archivecleanup -n "${ARCHIVEDIR}" "${LAST_BACKUP}" "%r"| find -type f -mmin +30 | xargs ls -lh - Then I get the error: pg_archivecleanup: too many parameters


texts: State of the art self healing and self protection in DBMS

texts:
At the moment I'm writing a paper and I need some input about the state of the art of self healing and self protection in DBMSs. Does somebody read some interesting articles or books about this topics in the last few month? Doesnt matter which DBMS you have read about.

Regards


texts: Get SSMS to show the time a query completed at?

texts:
I'm using Sql Server Management Studio 2012, and I'm wondering if there is a way to have a query show me at what time it was run and/or completed at. Basically just some kind of timestamp so that I know if I actually ran the query recently or not.

Thanks


texts: Permission denied for view beyond its own schema

texts:
There are two schemes rohdaten_fiat and staging_fiat. I have created a view in the latter one based on the first one.
CREATE VIEW staging_fiat.udz_odx_dtcs AS
SELECT * FROM rohdaten_fiat.udz_odx_relational_dtcs;

While keeping the database connection alive I can access the view, e. g.
SELECT * FROM staging_fiat.udz_odx_dtcs;

It runs under the following parameters:
SELECT current_user, current_setting('search_path'::text) AS search_path
     , relowner::regrole, relnamespace::regnamespace, relname, relacl
     , pg_get_viewdef(c.oid) AS view_definition
FROM   pg_class c
WHERE  relname = 'udz_odx_dtcs' OR relname = 'udz_odx_relational_dtcs';

See: https://pastebin.com/Hn7jAUat
However, after closing the connection and reconnect to the database, I can not:
SELECT * FROM staging_fiat.udz_odx_dtcs;

ERROR:  permission denied for relation view

The environmental parameters are the same (see https://pastebin.com/Hn7jAUat). Curiously, I can execute the query of the view manually:
SELECT * FROM rohdaten_fiat.udz_odx_relational_dtcs;

Only the view does not work. Where get the permissions lost? And why?
It's PostgreSQL 10.12. The connection including the search_path is absolutely identical.


texts: Pentaho Data Integration - Kettle- Update Identity Column in Microsoft SQL Server

texts:
For migration purposes I need to migrate existing ID's from my old database to my new table in MS SQL Server.

I can truncate my table in MS SQL Server and then adjust my database connection in Pentaho to 'SET IDENTITY_INSERT tablename ON'. Then I am able to insert my ID's through a table output step, but I want to be able to UPDATE my existing ID without truncating my table in MS SQL Server.

Could this be possible using Pentaho?


texts: Recommended maximum memory setting for PostgreSQL 9.4+ with Huge Pages

texts:
Since PostgreSQL 9.4+ supports Linux Huge Pages, I have configured a Ubuntu 16.04 server with 16GB of RAM and 8 CPU cores to use Huge Pages with a dedicated PostgreSQL 9.5 instance.

The estimate in https://www.postgresql.org/docs/devel/static/kernel-resources.html#LINUX-HUGE-PAGES is based on VmPeak value wich is rougly a little more that the shared_buffers value.

So the question is: Since the recommended value for shared_buffers is between 25% and 40% and since PostgreSQL also relies on the operating system cache.

What is the recommended value for Huge Pages? Should I configure almost all memory to Huge Page use of PostgreSQL (12G-14G) and shared_buffers to 6G, or, which I think is more correct, configure Huge Pages with ~6.5G and shared_buffers to 6G.


texts:
I have a table to update with no indexing in the where clause so I use ROWLOCK in the update script, hoping to get row lock instead of table lock but no luck.. so what is the function of the ROWLOCK then? I was using it in the select statement but still locking the entire table... so annoying!

  DBCC TRACEON (-1, 3604, 1200)

  BEGIN TRAN

  UPDATE [Order]

  with (ROWLOCK)

  SET ProductId = 3

  WHERE CustomerId = 1

TRACE OUTPUT:

  DBCC execution completed. If DBCC printed error messages, contact your
  system administrator.

  Process 54 acquiring IX lock on OBJECT: 16:229575856:0 (class
  bit2000000 ref1) result: OK

  Process 54 acquiring IU lock on PAGE: 16:1:196 (class bit0 ref1)
  result: OK

  Process 54 acquiring U lock on RID: 16:1:196:0 (class bit0 ref1)
  result: OK

  Process 54 acquiring IX lock on PAGE: 16:1:196 (class bit2000000 ref0)
  result: OK

  Process 54 acquiring X lock on RID: 16:1:196:0 (class bit2000000 ref0)
  result: OK

  Process 54 releasing lock reference on RID: 16:1:196:0

  Process 54 acquiring U lock on RID: 16:1:196:1 (class bit0 ref1)
  result: OK

  Process 54 releasing lock on RID: 16:1:196:1

  Process 54 releasing lock reference on PAGE: 16:1:196

  (1 row(s) affected)


texts: I've heard a long time ago that there is this tool that helps you tweek mysql settings for better performance but i can't seam to find it. I am aware that I can use ab for apache to simulate high traffic and it will generate me a slow log. However, if it crashes (already happened and it was in production mode), I don't know why it crashed and if it can be tweeked from the config.


texts: SSMS Restore Database Wizard questions while restoring to different destination

texts:
I need to pull some data from an earlier backup of a current prod database, so I'm using the SSMS Restore Wizard to run a point in time restore and put it into a temporary restore database (for example, source DB=MyProdDB, Destination=TemporaryRestoreDB).

In addition to changing the name of the destination DB in the General page of the SSMS Restore wizard to be TemporaryRestoreDB, do I also need to modify the file names/locations located in the Files tab to not be the same as the MyProdDB names?

Essentially, I'm just double checking if SSMS is smart enough to automatically detect that since it's a different destination on the General tab that it needs new .mdf and .ldf files rather than the existing ones. I tried searching, however didn't seem to have much luck with finding an answer.

Edit: Please make sure to see Tibor Karaszi's comment on the answer below, SSMS 2012 has different requirements than other versions of SSMS on restoring to a different database/files.


texts:
I'm using BIDS to create a cube in SSAS 2008 r2.

I used the wizard to create a time dimension.  Part of that configuration is selecting a date range.

After creation, I realized that I needed a wider date range than I originally specified.

I did find that I could open the dimension, go to properties, and redefine the date range there.  I saved and reprocessed the dimension, but the actual date range in the table is not growing to include the newly-added dates.

Is there another way to get this time dimension to grow or do I need to recreate it from scratch?


texts: Create a DELETE statement that deletes Models that haven’t had a purchase since December 31st 2014

texts:
We are given 5 tables.
Orders
    Order number (Primary Key)

    Date of order

    Customer ID (Primary Key for Customers table)

    Serial Number (Primary Key for Machines table)

Customers
    ID (Primary Key)

    Name

    Phone Number

    Street (ie. 505 Ramapo Valley Road)

    City (ie. Mahwah)

    State

    Zip

Machines
    Serial Number (Primary Key)

    Model Number (Primary Key for Models table)

Model
    ID (Primary key)

    Speed

    RAM

    HD

    Price

    Manufacturer ID (Primary key for Manufacturer table)

Manufacturer
    ID (Primary Key)

    Name

    Phone Number

    Email Address

Our task is to create an SQL statement which will delete tuples from the Models table which haven't had a purchase since December 31st 2014.
The following is what I have managed to come up with thus far:
DELETE FROM Model

WHERE ID NOT IN

(

    SELECT Model.ID

    FROM Model

    INNER JOIN Machines

        ON Model.ID = Machines.ModelNo

    INNER JOIN Orders

        ON Machines.SerialNo = Orders.SerialNo

    WHERE OrderDate > 2015

    GROUP BY Model.ID

);

Please let me know how I can fix this SQL statement, I believe the problem is either in the first or second line or possibly both.


texts: Unable to Force a WSFC Cluster to Start Without a Quorum

texts:
I was testing a three node AlwaysOn availability group cluster.  SQL Server  is on Node 1 and I powered off Node 1 and Node 3 simultaneously.  The cluster lost its quorum and appear to be down.  I tried following "Force a WSFC Cluster to Start Without a Quorum", but I am unable to bring the cluster online.  It is stuck is "joining" state.

What can we do now?

cluster log

Initially the cluster service is down.  After executing the Start-ClusterNode command it goes into running state. I have tried restarting the cluster service and also restarting the node, but can't force quorum.


texts: Connection to secondary DB in AlwaysOn High Availability

texts:
I need some clarification regarding Always on avilabilty group in SQL SERVE 2016 connection to the secondary DB. Now i have availability group with 2 nodes.

For the main application connection string I am expecting it to be  this
SQLSERVER=Listener;databseName=unicas_ux;ApplicationIntent=ReadOnly

If I want another report application to connect to the secondary DB. The connection string should be point to secondary  or the listener
Also, i when i try to connect to the  databases secondary server with a SQL authentication user i failed.

This has been solved by make the SID in secondary match the primary SID


texts: MySQL 8.0.22 Trigger won't run unless definer is logged in

texts:
I have the following tigger that used to work before upgrading to MySQL 8 from MySQL 5.  Now the trigger will only run if the definer is logged in via phpmyadmin or some other database admin tool. The trigger is setup as timing After and event Insert.
 CREATE TRIGGER `countupdate` AFTER INSERT ON `status`
 FOR EACH ROW BEGIN
    SET @COUNT=(SELECT COUNT(*) FROM `status` WHERE status.`systemname`= NEW.`systemname`);
       IF @COUNT > 0 THEN
        IF NEW.`status` = 'UP'
        THEN
        UPDATE `updowncount`
        SET `upcount` = `upcount` +1, lastupdate = NOW()
        WHERE systemname = NEW.`systemname`;
        ELSE
        UPDATE `updowncount`
        SET `downcount` = `downcount` +1, lastupdate = NOW()
        WHERE systemname = NEW.`systemname`;
        END IF;
    END IF;
 END

I have tried deleting the trigger then creating it again and setting the definer to root@%. I also tried definer root@localhost.
Running on Ubuntu 20.04.1
UPDATE:
Also it appears that I have to go back in and re-save the trigger or it won't trigger.
Log file during an insert that did not update data based on the tigger:
2020-12-14T02:06:09.569622Z   794 Query INSERT INTO status (systemname, status, lastchecked) VALUES ('Linuxdb2', 'Up', '2020-12-13 20:06:09.569394')
2020-12-14T02:06:09.569808Z   794 Query SET @COUNT=(SELECT COUNT(*) FROM `updowncount` WHERE updowncount.`systemname`= NEW.`systemname`)
2020-12-14T02:06:09.570132Z   794 Query SET @COUNT=(SELECT COUNT(*) FROM `status` WHERE status.`systemname`= NEW.`systemname`)
2020-12-14T02:06:09.574412Z   794 Query COMMIT

When status table has a new row inserted two tiggers run. The one above that has stopped working and one to add the system to the updowncount table. If the systemname doesn't exist in updowncount table.  This is a before for insert trigger.
 CREATE TRIGGER `countsystemadd` BEFORE INSERT ON `status`
 FOR EACH ROW BEGIN
       SET @COUNT=(SELECT COUNT(*) FROM `updowncount` WHERE updowncount.`systemname`= NEW.`systemname`);
       IF @COUNT=0 THEN
        INSERT INTO `updowncount` (upcount, downcount, systemname, `lastupdate`) VALUES (0, 0, NEW.`systemname`, NOW());
       END IF;
END

Thirteen new rows are all added with in seconds of each other by different python scripts.
Status table:
CREATE TABLE `status` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `systemname` char(40) DEFAULT NULL,
  `status` char(40) DEFAULT NULL,
  `lastchecked` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=444 DEFAULT CHARSET=utf8

Updowncount table:
CREATE TABLE `updowncount` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `upcount` int DEFAULT NULL,
  `downcount` int DEFAULT NULL,
  `systemname` text,
  `lastupdate` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8

UPDATE:
I removed the countsystemadd trigger and things started working. But once the data in the status table gets over 1,000 records the it stops working. This what shows up in the log.
2021-01-08T15:00:01.829948Z  1348 Query INSERT INTO status (systemname, status, lastchecked) VALUES ('msmccam', 'Up', '2021-01-08 09:00:01.829712')
2021-01-08T15:00:01.830210Z  1348 Query SET @COUNT=(SELECT COUNT(*) FROM `status` WHERE status.`systemname`= NEW.`systemname`)
2021-01-08T15:00:01.831031Z  1348 Query COMMIT


texts: How do I automatically grant all privileges to a schema created by a user in mysql?

texts:
Someone new joined the company and I created an account for her in mysql using the following:

CREATE USER 'username' IDENTIFIED BY 'password'

She needs to create her own schema and so used:

GRANT CREATE ON *.* TO username;

The problem is that now she is able to create new schemas and tables but she cannot do anything with it. I'd like her not to have access to existing schemas/tables but be able to create her own db and do anything with it. Is this possible?


texts: Access 2010 Need help connecting/updating database data

texts:
I have a large database that has sporadic fields with incomplete or missing data.  Quite the inherited project, I have to say.  I'm new to Access, so if you have any ideas on how to simplify this pile of awesomeness, I'd love to hear them.  Here's a bit of a primer on my situation:

Columns in tblMaster(yes, it's a mess.  Manufacturing what we do uses a lot of parts):
itemNumber
fittingNumber 1-13
fittingQuantity 1-13
fittingDescription 1-13
fittingInfo 1-13
fittingColor 1-13

Columns in tblDescriptions:
fittingNumber
fittingDescription
fittingInfo
fittingColor

This sprawls over thousands of records.  The problem is that the table of descriptions was incomplete when it was initially imported from Excel, and thus there's a ton of records in the main table with missing descriptions.  I have since updated the descriptions table information to include everything that was missing previously.  My entry form uses the updated information just fine, but the stuff prior is incomplete.  Here's what the tables look like.  tblMaster has columns for the fitting IDs, though they're not in the screenshot.

My question is this:  If each of those fitting columns in tblMaster is a separate fitting, how do I match each of those IDs to their respective descriptions in the other table and update the main table with the completed information?  I've been looking all over the place on how to do this; All I've seen is stuff on lookup tables and I don't want to use those for obvious reasons.  I'm about to scrap it and start over.

I would greatly appreciate any input on this.
Thanks


texts: MySQL: filter date column using LIKE or MONTH?

texts:
I have a CRON task that needs to extract customers with birthdays in a given month, from a MySQL InnoDB table. The birthday field is indexed and of type DATE.

Filtering for April, I can query the customers table either by:

SELECT *
FROM customers
WHERE birthday LIKE "2015/04/%";

or:

SELECT *
FROM customers
WHERE MONTH(birthday) = 4;

Which one would you recommend, and why?


texts: How do you create a composite foreign key when key being referenced is also a foreign key

texts:
If you have table a (column x) which is 1 to many with table b (columns x, y) which is 1 to many with table c (columns y, z), it's pretty straight forward to define the foreign keys.

If for performance reasons, table c was altered to be (columns x, y, z) is there a way to specify that table c (columns x, y) have a foreign key relationship to table b's (columns x, y)?

Previously table c contained 2 single column foreign keys x --> table a and y --> table b.  While this is correct, it creates erroneous results when trying to use commercial tools to create database diagrams.


texts: How to retrieve subtotal for order lines (rollup does not exist in sql 2000)

texts: Currently in a crystal report I have each order grouped together where the user is displayed the order lines in the orders group. I have made a running total field for a subtotal for all of the order lines prices. But I wan't to make a parameter for the end user to select whether the price is > or < than a specific amount. And what I thought may be the best solution is calculate the subtotal in the stored procedure and pass it to the report to build the parameter off of. But it looks like SQL Server 2000 does not contain the ROLLUP function in later versions.


texts: When is a lock acquired during a trigger's execution?

texts:
Following Situation
I'm trying to determine when a lock is acquired during the execution/firing of a trigger. I have the following setup.
Table Definition
CREATE TABLE a(
  id serial PRIMARY KEY,
  data boolean NOT NULL DEFAULT FALSE
 );

Function
CREATE FUNCTION trig_func_before_update_a() RETURNS trigger AS $$
BEGIN
  RAISE NOTICE 't2 starts here';
  RETURN NEW;
END
$$ LANGUAGE plpgsql;

Trigger Definition
CREATE TRIGGER trig_test BEFORE UPDATE ON a
  FOR EACH ROW EXECUTE PROCEDURE trig_func_before_update_a();

Testing
Suppose we have two concurrent transactions
Transaction t1
UPDATE a SET data=data IS NOT data WHERE id=1;

The transaction t1 reaches the RAISE NOTICE inside the trigger and then the transaction t2 is concurrently started:
Transaction t2
PERFORM FROM a WHERE id=1 FOR UPDATE;

Locking Behaviour
After some tests, the row seems locked before the trigger is executed.
I found nothing in documentation speaking about that.
Question
Is there any information about this behaviour or are there certain caveats I am hitting?
Solution
Following @a_horse_with_no_name's comment

"A trigger doesn't acquire a lock on its own. The DML statement firing
the trigger will acquire a lock."


texts: innodb update with limit locks all matching (ignores limit)

texts:
For a query similar to this:

UPDATE myinnotable1 SET value=5,updated=now(),status=1 WHERE status>10 AND message="match msg" LIMIT 1;

I found that innodb engine locks ALL rows matching criteria message="match msg", update single row and unlock all rows again.

Is there anyway to minimize number of locked rows to the required locked rows? The problem with this lock is that it takes long time and sometimes give me SQL deadlocks (with other update queries) .

EDIT:

1) Table

CREATE TABLE `myinnotable1` (
  `id` int(11) NOT NULL,
  `value` int(11) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `message` varchar(20) DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  KEY `index2` (`status`,`message`,`updated`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2) Table has millions of rows (~10M), with status>10 && message="match msg" matches around 10K rows.

3) All  rows matching status and message are equal, I want to update any one in them (that is why I don't use order by).

4) What I want to do is: - I have many worker threads, which marks a row in the table (update), select the row (select)  and update it after processing (update). This is done until all rows matching status>10 && message="match msg" ends.


texts: Should child comment be treated as separate table?

texts:
I'm building a commenting system, currently my table indicates which comment is child with parent id in 1 table, I would like to know what is the best way of doing this?

Current Table Scheme (1 Table):

CREATE TABLE `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent` int(11) DEFAULT NULL,
  `post` int(11) NOT NULL,
  `comment` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
  PRIMARY KEY (`id`),
  KEY `child_idx` (`parent`,`id`),
  KEY `post_FK_idx` (`post`),
  CONSTRAINT `post_comment_FK` FOREIGN KEY (`post`) REFERENCES `posts` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=158 DEFAULT CHARSET=latin1

Question:

Should I add a second table for the child and do a JOIN when retrieving The comments?
If child can be in 1 table, how should I delete all children when parent get deleted? (I have read about Foreign Key, but that doesn't work on 1 table.)

Foreign Key:

When I create a Foreign Key on 1 table, it works and get created, but when I try to enter data I get the following error.

Cannot add or update a child row: a foreign key constraint fails
(`App`.`comments`, CONSTRAINT `child_comment_FK` FOREIGN KEY (`parent`)
REFERENCES `comments` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)"

Statement:

INSERT INTO comments SET
    parent = :parent,
    post = :post,
    comment = :comment;

Screenshot of Error:


texts:
I'm trying to understand how Oracle determine if the index can be used for constraint.
For example, the following works :

create table temp11
(id int not null,
val varchar2(10),
CONSTRAINT PK_temp11 primary key (id) using index
(create index IDX_temp11 ON temp11(id,val))
);
--Also, I can re-use the same index for another unique constraint :
ALTER TABLE temp11 ADD CONSTRAINT UQ_temp11 UNIQUE(id,val) using index IDX_temp11;

But if I change index definition and make it unique, I get error

  ORA-14196: Specified index cannot be used to enforce the constraint.

create table temp11
(id int not null,
val varchar2(10),
CONSTRAINT PK_temp11 primary key (id) using index
(create UNIQUE index IDXU_temp11 ON temp11(id,val))
); -- ORA-14196

** Making val not null doesn't make a difference.
Could anyone explain this behaviour?
I don't see why such an unique index cannot used for enforcing constraint whereas non-unique is accepted.
I use Oracle 10 if it matters.


texts: Optimizing a four-year-old MySQL database

texts:
I developed an application with MySQL 5.1 back-end four years ago. After that, I only gave remote support a couple of times, but that too for application crashes, and not for database. The database is still running well. The customer takes the backup on decided schedules. I want to optimize the database as I think in these years many records have been deleted and many added.

How do I tune up the database and bring it into proper shape? What things need attention?


texts: Schema transfer of SP does not update information_schema.routines.routine_definition / object_definition?

texts:
After a transfer of an SP from one schema to another, the information_schema.routines.routine_definition and object_definition() still show the original schema.

create schema SchemaA;
go
create proc SchemaA.Proc1 as begin   return 1;  /*jjj*/  end
go
create schema SchemaB;
go
alter schema SchemaB transfer SchemaA.Proc1;
go

select specific_catalog, Routine_schema, routine_type, Routine_definition
from information_schema.routines where Routine_definition like '%jjj%'

exec sp_recompile @objname = 'SchemaB.Proc1'; -- mark for recompile

exec SchemaB.Proc1; -- run it

-- Still wrong
select specific_catalog, Routine_schema, routine_type, Routine_definition
from information_schema.routines where Routine_definition like '%jjj%'

-- Also wrong
select object_definition(object_id('Sandbox.SchemaB.Proc1'))

Manually re-compiling it fixes the issue and we do have a moderately scary script to do that for all SPs but is there any other way to rebuild the information_schema tables?


texts: How to monitor SQL Server data and log file growth?

texts: I'm trying to find a way to monitor data and log file size from SQL Server databases (within an SQL Server instance). I would not want to use third party tools, but simply pure SQL or PowerShell. I know that one can query this data from sys.database_files or use DBCC SQLPERF(logspace) to get data from log file usage. Is there any way to automate this monitoring and get reports from this data? Any ideas and code samples are helpful!