texts: Database Design for Preserving Values and Additional columns

texts:
My colleague and I are having some trouble coming up with a database design, and I am doing my best to stay away from EAV for now.

We have an entity called Preparation that stores configuration data for machines. There are two types of processes per machine, let's call these process1 and process2, and a machine can only have one process. Both processes share a lot of attributes so setting up one table seemed logical.

However process2 has a lot of extra data, and even sub-categorizes itself with process2a, processes2b that is specific to those sub categories (process1 only has one extra attribute for now). So that one table will have nulls in columns that are not specific to its process.

Splitting the table for each process was another option. However, we need to preserve any data change and splitting the tables will bring more work (most all transactions will be INSERTS with a timestamp into a history table, followed by an UPDATE on the live table if they are updating a record). For example, if a machine changes from process1 to process2, then a record will have to be inserted in the history table from process1, deleted from process1 and then inserted into process2. We are using the standard edition of SQL Server, so no CDC to help with logging changes.

I am trying to keep these pretty relational and I would hate to create one table with expected NULLS (maybe create SPARSE columns if the NULL percentage is high?).

I am hoping someone has experience with this type of situation. Thank you.


texts: Upserting 50K records into big table (app. 350 columns) in postgresql

texts:
Using C# I'm trying to upsert from csv file (~ 55000 records) to a postgresql table with 350 columns. I'm just building the upsert statements from header and record which are comma separated. Like this:
INSERT INTO public.table(field1,fields2,field3, ..... ,field350) VALUES(?,?,?,?,?,.......,?) ON CONFLICT(field1) DO UPDATE SET ...;
INSERT INTO public.table(field1,fields2,field3, ..... ,field350) VALUES(?,?,?,?,?,.......,?) ON CONFLICT(field1) DO UPDATE SET ...;
INSERT INTO public.table(field1,fields2,field3, ..... ,field350) VALUES(?,?,?,?,?,.......,?) ON CONFLICT(field1) DO UPDATE SET ...;
INSERT INTO public.table(field1,fields2,field3, ..... ,field350) VALUES(?,?,?,?,?,.......,?) ON CONFLICT(field1) DO UPDATE SET ...;
....etc

I know about the bulk insert Copy operation which gave me for the inserts a very good peformance, but as mentioned, I need to run upserts and I didn't find anyway to run these upserts using this COPY thing.
Moreover, I can't run these in transcations because we want to check line by line and if a line fails, we don't want to fail the whole process, but log exception and continue with next line.
Using a stored procedure also didn't work because of the big number of columns that we have (350) which exceeds the allowed (100).
With my C# script i'm achieving 50K in 2 minutes, I know that this can be optimized. So is there any effecient way that you can suggest it to me?


texts: Concatenate with NULL values ( Should not replace NULL )

texts:
After some time searching for an answer, I got a LOT of question saying to use coalesce,concat(), and etc, but what I need is:

Table:

create table nulos ( nome varchar(max))
alter table nulos add cidade varchar(max)

values:

insert into nulos values ( 'rafael','são paulo'),('juliana',null)

I need to concatenate the values inside this table, to a insert, like this:

 select 'insert into nulos (nome,cidade) values ('+nome+','+cidade+')'
from nulos

And the result of the select:

insert into nulos (nome,cidade) values (rafael,são paulo)
NULL

how can I use the NULL value inside this concatenation? every answer says something to replace the null with '' or '_', But what I need it this:

insert into nulos (nome,cidade) values (rafael,são paulo)

insert into nulos (nome,cidade) values (Juliana,NULL)

SET CONCAT_NULL_YIELDS_NULL on is not an option because it just deletes the NULL value, and I need to insert it.


texts: Make Oracle SQL Developer act like PL/SQL Developer

texts:
Is it possible to configure Oracle SQL Developer v 4.03 to automatically display tables while I'm typing in names like in PL\SQL Developer?

Second question: How to make output grid look like PL/SQL Developer? Is it possible?

Is it possible to change default colors in zebra output? I have enabled 'Zebra pattern'

Preferences->Database->Worksheet->Grid in checker board or Zebra pattern

...but default colors are so...ugly. It would be great to change also default NULL values (change field color and (null) to just NULL). Could you add option to set user's color for NULL and zebra coloring? What if I want to use magenta or cyan?


texts: What is the reasoning behind the CAP theorem?

texts:
http://en.wikipedia.org/wiki/CAP_theorem

http://www.cs.berkeley.edu/~brewer/cs262b-2004/PODC-keynote.pdf

I think it is not very straightforward why only two of

Consistency
Availability
Partition tolerance

can hold for any given distributed database system. This conjecture was proved but is there an easier way to see why probably this might hold?

I am not looking for a proof, just a good way to understand why this theorem might make sense. What is the reasoning?


texts:
In the following queries the Student table has Clustered Index on StudentID column, and the Enrollment Table also has a Clustered Index on EnrollmentID as well as Non-Clustered index on (SessionID, SemesterID, ShiftID, OtherSemester).

First Query

Second Query

Why the second plan uses look up while both OtherSemester and ShiftID are included in non-clustered index? Why the second query does not have the plan like the first query?


texts: What is the difference between ROLE and PRIVILEGE for Oracle?

texts: Is role a collection of certain privileges?


texts: Capturing the Login Failure alerts specifc to bad password attempts

texts:
I am unable to figure out the way to capture only those failed logins, which have failed with the error:

  Login failed for user X. The password did not match...

Yes I can read that info out from SQL Server error logs and Event Viewer, but the problem is on some servers due to space constraints we are not keeping frequent error logs.

Moreover, what I am trying to achieve here is to find the culprit host or application which is trying to connect with a bad password and later gets the account locked out. Therefore after some time when we try to review the error log messages are full with lock out, but basic password mismatch error log is truncated.

Is there a way to only capture/track and save the information related to bad password attempts for those logins and capturing required details?


texts: TSQL issues with view and data operations

texts:
I can't get my head around this sooo - help! ;)

Working with SQL Server here.

I have a few tables that hold customer sales, products, customer and other data imported from SAP.

I have create a view that combined all this data into a single easy to read "table". The only caveat being the data coming from SAP for each sale is group by sale period of month.year (09.2018, 10.2018, 11.2018, etc).

My import script break this data into two columns, month & year.
Now, because I will have to run queries with date function on this view I needed a datetime field, so in the view definition I essentially did this:

CONVERT(datetime, CONCAT(a.period_year,'-',a.period_month,'-01')) AS period_date

first create a string value of the date adding day (01) and then convert this to datetime type.

This view will not hold any future data, but for the sake of argument & testing I run this query:

SELECT * FROM my_sales_view WHERE period_date <= getdate();

I get all records returned to me, no problem.
But, let say I need all data from past 12 months, from entire month, meaning if I run the query on 15th of May 2019 I need records from 1st of June 2018, not 16 of June.

This will give me that date:

SELECT DATEADD(month, DATEDIFF(month, -12, getdate()), 0)

so, I though by running this I would get what I want:

SELECT * FROM my_sales_view WHERE period_date > DATEADD(month, DATEDIFF(month, -12, getdate()), 0)

But I don't. Instead the query returns the following error:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

However when I run this:

SELECT * FROM my_sales_view WHERE period_date < DATEADD(month, DATEDIFF(month, -12, getdate()), 0)

by just inverting the operator and getting the date older then 1 year ago it works fine!

So, what gives?! This makes no sense to me, what am I missing here.


texts: SSL Configuration fails with a warning MySQL 8.0

texts:
Hi I am trying to set up a MySQL server 8.0.16 on RHEL 7.5. The configuration I am willing to include for this server contains SSL related options.

The MySQL server is initialized but with a warning related to SSL, which is as follows:

mysqld --defaults-file=/mysql_data/config/options.cnf --initialize --user=mysql &

[root@BFLBRESRV03 ~]#  100
 100 200 300 400 500
 100 200 300 400 500
 100
2019-12-13T13:04:14.071486Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2019-12-13T13:04:14.071739Z 0 [System] [MY-013169] [Server] /mysql_binaries/mysql/mysql-server-8.0.16/bin/mysqld (mysqld 8.0.16-commercial) initializing of server in progress as process 962
2019-12-13T13:04:19.318987Z 0 [Warning] [MY-010069] [Server] Failed to set up SSL because of the following SSL library error: SSL_CTX_set_default_verify_paths failed
2019-12-13T13:04:19.498482Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: geY0lgrNZd+u
2019-12-13T13:04:20.958898Z 0 [System] [MY-013170] [Server] /mysql_binaries/mysql/mysql-server-8.0.16/bin/mysqld (mysqld 8.0.16-commercial) initializing of server has completed

[1]+  Done                    mysqld --defaults-file=/mysql_data/config/options.cnf --initialize --user=mysql

[Warning] [MY-010069] [Server] Failed to set up SSL because of the following SSL library error: SSL_CTX_set_default_verify_paths failed

The directory does not contain the ca.pem file but it does contain private_key.pem, and public_key.pem.

My options file contains following ssl configuration

[mysqld]

#       SSL     #

ssl-cipher                                          =               DHE-RSA-AES256-SHA

ssl_ca                                               =               /mysql_data/ssl-certs/ca.pem

ssl_cert                                             =               /mysql_data/ssl-certs/server-cert.pem

ssl_key                                             =               /mysql_data/ssl-certs/server-key.pem

require_secure_transport                 =               ON

tls_version                                        =               TLSv1.2

If I initialize the server without ssl-ca, ssl-cert, ssl-key options, the server is successfully initialized without any warnings and the data directory contains the files ca-key.pem,  client-cert.pem, public_key.pem, server-key.pem, ca.pem, client-key.pem, private_key.pem, server-cert.pem.

Kindly help me understand what mistake I am doing. What is the warning about and why the ca.pem certificate file was not created?

Can I create the SSL certificates in directories other than data directory?

I know i can later create the certificates using openssl or mysql_ssl_rsa_setup.

https://serverfault.com/questions/839650/mysql-ssl-ssl-ctx-set-default-verify-paths-failed

My SELinux is in PERMISSIVE mode.

MySQL ssl setup failed

The directory for SSL certificates that I was using /mysql_data/ssl-certs is owned by mysql user and group.


texts: How to trace the exact query fired in sql server

texts:
I am facing a problem with performance tuning. We are using an application called ARAS (one of the leading PLM software) which is using Sql server as the back end.

Every time we find some data using Aras interface it interacts with SQL server and give us Response.

But I am unable to find which query is being sent to SQL Server. I tried to find it using this query

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC.

This was also not helping me properly.

We are using SQL Server 2008 Express and ARAS 9.3


texts: Two rows having null values merge into one row without null values

texts:
I have fetched the record from table like this:

Name                 Opp                     Bid                  Pro
----------------------------------------------------------------------
Admin                 2                      NULL                 NULL
Pragnya Sonal         7                      NULL                 NULL
Priyanka Debnath      17                     NULL                 NULL
Sanjeev Sasmal        2                      NULL                 NULL
Subrajeet Sahoo       1                      NULL                 NULL
Pragnya Sonal         NULL                   2                    NULL
Pragnya Sonal         NULL                   NULL                 1

But I want to reorder this table in this format:

Admin                 2                      NULL                 NULL
Pragnya Sonal         7                      2                    1
Priyanka Debnath      17                     NULL                 NULL
Sanjeev Sasmal        2                      NULL                 NULL
Subrajeet Sahoo       1                      NULL                 NULL

Can any one help me to solve this, thank you very much in advance


texts: Counting Grouped records, using start date and end date with NULLs

texts:
Here is my issue, Source DB is Azure MSSQL - I have a table that lists all the subscriptions sold. The Relevant columns are:
Account Number, Subscription Group, Purchase Date, Cancellation Date
Cancellation Date contains NULL values (for Subscriptions that have yet to be cancelled). An Account may have multiple subscriptions, across multiple subscription groups.
Sample data is:
Account number | Subscription group | Purchase date | Cancellation date |
1 | A | 2/4/2013 | 2/4/2014 |
1 | A | 2/4/2013 | NULL |
1 | B | 2/4/2013 | NULL |
2 | C | 5/7/2018 | 5/8/2020 |
3 | C | 20/3/2020 | NULL |
3 | C | 4/8/2020 | NULL |
Etc.
I am looking to count the number of Subscription Groups for each customer and for each month - so the output would look something like this:
Customer | Count of A | Count of B | Count of C | Month/Year |
1 | 1 | 1 | 0 | 10/2020 |
3 | 0 | 0 | 2 | 10/2020 |
1 | 1 | 1 | 0 | 09/2020 |
3 | 0 | 0 | 2 | 09/2020 |
1 | 1 | 1 | 0 | 08/2020 |
2 | 0 | 0 | 1 | 08/2020 |
3 | 0 | 0 | 2 | 08/2020 |
I can write the query for a given month:
select t.[account number],
COUNT(CASE WHEN t.[Subscription group] = A then 1 ELSE NULL END) as "Count of A",
COUNT(CASE WHEN t.[Subscription group] = B then 1 ELSE NULL END) as "Count of B",
COUNT(CASE WHEN t.[Subscription group] = C then 1 ELSE NULL END) as "Count of C",
'09/2020' as 'Month/Year'
from table t
where (t.[Purchase Date] <= '30/09/2020' and (t.[Cancellation Date] is null or t.[Cancellation Date] > '01/10/2020') )
Group by t.[Account Number], [Month/Year]
However, I'm hoping someone can help me modify the above so it will run for All months (based on MIN from Purchase date and MAX on Cancellation date)


texts:
Any command to show the usage of a command or a SQL statement?

Such as (though none works here):

.help schema
.help .schema
.help INSERT
.help SELECT


texts: How to configure read transactions to never cause other write serialization transactions to fail

texts:
I am protecting critical transactions with application mutexes and SERIALIZABLE.

I presume that this makes these operations as acid as possible, but now I'm concerned that reads might cause failures to those write transactions because of locking.

The reads are in libpqxx prepared statement transactions.  I have read the docs, wiki, and white paper, but I can't find out how to configure a read transaction so that it never risks a failure of another critical serializable write transactions.

I cannot determine if locks will cause serialization failures.  Can they?

The reads do not need to be perfectly acid because the application compensates for race conditions, old data, etc.  The primary concern is that the serializable critical write transactions do not fail due to other reads.

How should a read only prepared statement be configured so absolutely not to risk another serializable transaction write failure?


texts: After many successive and successful queries SQL Server starts refusing connections

texts:
I have a GraphQL server ( Node v14.15.1 ) which uses typeorm to connect to a SQL Server 12.0.4100.1, also it runs inside a docker container, connection parameters are:
  let connection = await createConnection({
    type: "mssql",
    host: HOST,
    port: PORT,
    database: DB,
    username: USER,
    password: PASSWORD,
    // NOTE: Use typeorm-model-generator for Entities
    logging: false,
    synchronize: true,
    requestTimeout: 300000,
    connectionTimeout: 300000,
    entities: ["./models/mssql"],
    pool: {
      max: 1000, min: 1,
      idleTimeoutMillis: 3000,
      evictionRunIntervalMillis: 1500000,
    },
    options: {
      encrypt: false,
    }
  })

Problem:
After numerous sucessful queries the SQL Server starts refusing connections with the error:
ConnectionError: Failed to connect to adress: port - connect ECONNREFUSED adress: port\n
    at ConnectionError (/home/raa/code/wcc-gql/node_modules/mssql/node_modules/tedious/lib/errors.js: 13: 12)\n
    at Connection.socketError (/home/raa/code/wcc-gql/node_modules/mssql/node_modules/tedious/lib/connection.js: 1290: 56)\n
    at _connector.Connector.execute (/home/raa/code/wcc-gql/node_modules/mssql/node_modules/tedious/lib/connection.js: 1116: 21)\n
    at Socket.onError (/home/raa/code/wcc-gql/node_modules/mssql/node_modules/tedious/lib/connector.js: 140: 7)\n
    at Socket.emit (events.js: 198: 13)\n
    at emitErrorNT (internal/streams/destroy.js: 91: 8)\n
    at emitErrorAndCloseNT (internal/streams/destroy.js: 59: 3)\n
    at process._tickCallback (internal/process/next_tick.js: 63: 19)'

The problem happens when query a specific type which looking down the hierarchy has a lot of resolvers, also the amount of data varies.
The more data the more likely that the problem will occur.
What also interesting is that it is more likely that the problem will occur if the program is running inside a container or as PM2 service.
I've tested the same data where connections are refused inside a container and not refused in VS Code debugger or when run from terminal.
I tried fiddling with the pool options which gave modest results.
I've also checked that I do not exceed the amount of connections allowed by the DB.
I've checked the logs on the SQL Server, there are no issues or errors in them at the time I make requests. I've just now added support for tracking log attempts on the SQL Server and it now registers some 776 successful logins from this application in period 15:03:46.01 - 15:04:48.07 and after that I assume the I get the errors.
EDIT:
I've tried changing the Network Packet Size option in Sql Server options and packetSize connection options of typeorm -> mssql -> tedious which didn't work but revealed some new details. Since I had to reset the server apply new Sql Options, immediately after the reset the queries successfully finished.
So I tried tracking the resources and saw that the server ramps up to 100% processor capacity pretty quickly and after that has problems opening new connections and if I allow a too large max in pool options (in my case 500) of connections it start refusing connections.
When I bring down the max property, however, a new type of error arises which manifests as connection timed out from unknown reason this time stemming from tarn.js.
What I think is happening is that the tarm.js(underlying connection pool) has a default timeout for createTimeoutMillis/ adding new connections to pool, and this option is not revealed through the typeorm API and while it waits on the server to unfreeze and allow a new connection to be allotted it times out.


texts:
I have two tables :  Part, OrderLine:

Part (PartNum PK, PartDesc, Price)

OrderLine (OrderNum PK, PartNum PK, QtyOrdered)

And I want to find out the parts that were not ordered. I think I have a good idea, but I may be missing something;  I think most likely on the syntax. Please check:

select Part.PartNum
    ,OrderLine.OrderNum
from OrderLine
inner join Part on OrderLine.PartNum = Part.PartNum
where PartNum not in (
        select distinct (OrderNum)
        from OrderLine
        )

Is this correct?


texts:
Please help me to create a correct MySql query, which I failed to do.

I have 2 tables in our DB:

CREATE TABLE IF NOT EXISTS `user_audit` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `audit_event` varchar(128) NOT NULL,
  `data` varchar(256) NOT NULL DEFAULT '',
  `source_ip` varchar(256) NOT NULL DEFAULT '',
  `user_agent` varchar(256) NOT NULL,
  `create_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=27181 ;

CREATE TABLE IF NOT EXISTS `user_balance_transactions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `audit_event_id` int(10) unsigned NOT NULL,
  `private_tournament_id` int(10) unsigned DEFAULT NULL,
  `regular_tournament_id` int(10) unsigned DEFAULT NULL,
  `points` int(11) NOT NULL,
  `description` varchar(256) NOT NULL DEFAULT '',
  `create_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `private_tournament_id` (`private_tournament_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1001 ;

I have 2 audit events that I'm interested in:

user_audit.audit_event = "RGL_TOURNAMENT_ENTRANCE_PAID"
user_audit.audit_event = "RGL_TOURNAMENT_ENTRANCE_REFUNDED"

These audit events are opposite to each other, meaning user can pay, then can be refunded, then use pays again and so on.

On top of these 2 table I have to build one SQL select statement that will return DISTINCT user_id, regular_tournament_id, create_timestamp of ALL USERS with:

user_balance_transactions.regular_tournament_id is not NULL
user_balance_transactions.audit_event_id is pointing to user_audit table where user_audit.audit_event = 'RGL_TOURNAMENT_ENTRANCE_PAID'
and it's the last event (most important!) that happened chronologically.

IMPORTANT: In user_audit table 2 possible audit events are related to each other: 'RGL_TOURNAMENT_ENTRANCE_PAID' and 'RGL_TOURNAMENT_ENTRANCE_REFUNDED',
but we need user_id only of such users who has  'RGL_TOURNAMENT_ENTRANCE_PAID' event as last.
create_timestamp is a timestamp of this last event -  'RGL_TOURNAMENT_ENTRANCE_PAID'

A. Test data for "user_balance_transactions" table:

id, user_ID, audit_event_id, private_tournament_id, regular_tournament_id, points, description, create_timestamp

2, 23, 1711, null, 77, 10, "credit", "2015-06-12T17:23:44"

3, 23, 1712, null, 77, -10, "debit", "2015-06-12T17:41:44"

4, 23, 1713, null, 77, 10, "credit", "2015-06-12T18:11:44"

B. Test data for "user_audit" table:

id, user_ID, audit_event, data, source_ip, user_agent, create_timestamp

1711, 23, "RGL_TOURNAMENT_ENTRANCE_PAID", "","","", "2015-06-12T17:23:44"

1712, 23, "RGL_TOURNAMENT_ENTRANCE_REFUNDED", "","","", "2015-06-12T17:41:44"

1713, 23, "RGL_TOURNAMENT_ENTRANCE_PAID", "","","", "2015-06-12T18:11:44"

C. Expected SQL output:

user_id, regular_tournament_id, create_timestamp

23, 77, "2015-06-12T18:11:44" (last RGL_TOURNAMENT_ENTRANCE_PAID event)

"user_balance_transactions" stores user transaction and "user_audit" stores all user audit events, of which some of them related to the balance transactions.

That's why "user_balance_transactions" table has a Foreign Key to "user_audit" table "audit_event_id"


texts: Can I monitor the progress of importing a large .sql file in sqlite3 using zenity --progress?

texts:
I'm trying to monitor the progress of a sqlite3 command importing a large .sql file into a database using zenity --progress.

I've tried the following which will import the file, however progress is not shown:

sqlite3 DATABASE < import_file.sql | zenity --progress --percentage=0 --auto-close

I know I need to provide zenity a numeric source for the progress, but don't really know how to obtain the number.

Can anyone help me?


texts: Unable to replicate schema changes in transactional replication

texts:
I have implemented a transactional replication on one of my dbs.

In the documents provided by Microsoftsql (here and here), I should be able to change the schema and that these changes will be replicated to the subscriber side as well. However, I haven't been able to do so.

Is there anything I am missing while setting up transactional replication?

I have used the following customized script in accordance to my requirements, basically replicating each and every column and everything associated(like a complete replica):

use [db_name]
exec sp_changearticle
    @publication = N'db_PUB',
    @article = N'table_name',
    @property =schema_option,
    @value= '0x000003454DFD7FDF',
    @force_invalidate_snapshot =0,
    @force_reinit_subscription =1
GO

The error I get while making changes is something like this:

  Cannot perform action on the table because it is published for replication.

I am thinking of a way to get over this issue, by removing the article from publication, make changes to it and then add it back to publication.

But every time I do this I have to generate a new snapshot and reinitialize the subscription, which doesn't seem to be much efficient.

What can I do to resolve this issue?
Any alternative?

exec sp_dropsubscription
        @publication =  'tTestFNames'
     ,  @article =  'tEmployees'
     ,  @subscriber =  'RSCOMPUTER'
     ,  @destination_db =  'testrep'

  exec sp_droparticle
        @publication =  'tTestFNames'
     ,  @article =  'tEmployees'

  alter table tEmployees alter column Forename varchar(100) null

  exec sp_addarticle
        @publication =  'tTestFNames'
     ,  @article =  'tEmployees'
     ,  @source_table =  'tEmployees'

  exec sp_addsubscription
        @publication =  'tTestFNames'
     ,  @article =  'tEmployees'
     ,  @subscriber =  'RSCOMPUTER'
     ,  @destination_db =  'testrep'

exec sp_startpublication_snapshot
     @publication = N'tTestFNames';
Go


texts: how to update new parent node in the middle of the postgresql ltree by some simple update SQL statement?

texts:
I follow this URL to make some SOP for my colleague.
How to update the parent/child of all rows in the tree (ltree)?

However, my colleague hope to use some simple statement about "UPDATE" for adding or deleting some parent nodes without complex Regular Expression statements.

Is there any better statement by using UPDATE? Thanks for advance.


texts:
I have a query which works fine in MySQL, however when I try to recreate the same query in PostgreSQL (same DB structure) I just get a syntax error.

MySQL:

SELECT ticket.queue_id, queue.name, article.create_time,
COUNT(article.id)
FROM article
JOIN ticket
JOIN queue
ON article.ticket_id=ticket.id
and ticket.queue_id=queue.id
GROUP BY ticket.queue_id

Postgres:

SELECT ticket.queue_id, queue.name, article.create_time,
sum(article.id) FROM article
JOIN ticket
JOIN queue
ON article.ticket_id=ticket.id
AND ticket.queue_id=queue.id
GROUP BY ticket.queue_id;

The error:

ERROR:  syntax error at or near "GROUP"
LINE 7: GROUP BY ticket.queue_id;
         ^

I'm quite at a loss here, as I can't really pinpoint my error.
The same error appears when I leave out the 'sum' statement in the query.

Any help much appreciated.


texts:
I have installed MariaDb hundreds of times on previous Ubuntu versions, but on Ubuntu 18.04 I'm struggling. After the installation finishes, once I restart the PC, MariaDB starts for around 2 minutes and then stops.

When I try to login to MySQL, I get the below error:

  ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory")

I was able to login before I restarted the PC with my username root and the root password. Why does everything change after the restart?

error log from /var/log/mysql/error.log

usr/sbin/mysqld: Too many arguments (first extra is 'start').

2018-09-02 17:35:48 139917209496704 [ERROR] Aborting

2018-09-02 17:38:00 140363564260480 [Note] InnoDB: innodb_empty_free_list_algorithm has been changed to legacy because of small buffer pool size. In order to use backoff, increase buffer pool at least up to 20MB.

2018-09-02 17:38:00 140363564260480 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2018-09-02 17:38:00 140363564260480 [Note] InnoDB: The InnoDB memory heap is disabled
2018-09-02 17:38:00 140363564260480 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-09-02 17:38:00 140363564260480 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2018-09-02 17:38:00 140363564260480 [Note] InnoDB: Compressed tables use zlib 1.2.11
2018-09-02 17:38:00 140363564260480 [Note] InnoDB: Using Linux native AIO
2018-09-02 17:38:00 140363564260480 [Note] InnoDB: Using SSE crc32 instructions
2018-09-02 17:38:00 140363564260480 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2018-09-02 17:38:00 140363564260480 [Note] InnoDB: Completed initialization of buffer pool
2018-09-02 17:38:00 140363564260480 [Note] InnoDB: Highest supported file format is Barracuda.
2018-09-02 17:38:00 140363564260480 [Note] InnoDB: The log sequence number 1683676 in ibdata file do not match the log sequence number 1683686 in the ib_logfiles!
2018-09-02 17:38:00 140363564260480 [Note] InnoDB: Restoring possible half-written data pages from the doublewrite buffer...
2018-09-02 17:38:00 140363564260480 [Note] InnoDB: 128 rollback segment(s) are active.
2018-09-02 17:38:00 140363564260480 [Note] InnoDB: Waiting for purge to start
2018-09-02 17:38:00 140363564260480 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.39-83.1 started; log sequence number 1683686
2018-09-02 17:38:00 140362895062784 [Note] InnoDB: Dumping buffer pool(s) not yet started
2018-09-02 17:38:00 140363564260480 [Note] Plugin 'FEEDBACK' is disabled.
2018-09-02 17:38:00 140363564260480 [Note] Recovering after a crash using tc.log
2018-09-02 17:38:00 140363564260480 [Note] Starting crash recovery...
2018-09-02 17:38:00 140363564260480 [Note] Crash recovery finished.
2018-09-02 17:38:00 140363564260480 [Note] Server socket created on IP: '127.0.0.1'.
2018-09-02 17:38:00 140363564260480 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.1.34-MariaDB-0ubuntu0.18.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Ubuntu 18.04
2018-09-02 17:39:30 140363563476736 [Note] /usr/sbin/mysqld: Normal shutdown
2018-09-02 17:39:30 140363563476736 [Note] Event Scheduler: Purging the queue. 0 events
2018-09-02 17:39:30 140362953770752 [Note] InnoDB: FTS optimize thread exiting.
2018-09-02 17:39:30 140363563476736 [Note] InnoDB: Starting shutdown...
2018-09-02 17:39:30 140363563476736 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool
2018-09-02 17:39:32 140363563476736 [Note] InnoDB: Shutdown completed; log sequence number 1683696
2018-09-02 17:39:32 140363563476736 [Note] /usr/sbin/mysqld: Shutdown complete
`


texts:
One of the column in my table was initially created as a varchar(1000). Now there is a need to increase the size of that column to hold around 5000 characters. I used the alter statement to increase the size of that column.

DESC table_name as well as SHOW CREATE TABLE table_name clearly indicates the new size of that column to be 5000 characters. However, interestingly when I try to insert any data exceeding 1000 characters ERROR 1406 (22001) : Data too long for column error shows up.

Out of desperation I changed the datatype to text, and still it's limited to 1000 characters. Any suggestion?

I created an other dummy table with a column size of varchar(5000) and that works just fine.

Engine used is InnoDB and Default Charset is UTF8.


texts: Improving distinct values estimates in Postgres

texts:
Full counts in Postgres can be slow, for reasons that are well-understood and much discussed. So, I've been using estimation techniques instead, where possible. For rows, pg_stats seems fine, for views, extracting an estimate returned by EXPLAIN works okay.

https://www.cybertec-postgresql.com/en/count-made-fast/

But what about distinct values? Here, I have had a lot less luck. Sometimes the estimates are 100% correct, sometimes they're off by factors of 2 or 20. Truncated tables seem to have badly stale estimates in particular (?).

I just ran this test and have provided some results:

analyze assembly_prods; -- Doing an ANLYZE to give pg_stats every help.

select 'count(*) distinct' as method,
        count(*) as count
from (select distinct assembly_id
      from assembly_prods) d
union all
select 'n_distinct from pg_stats' as method,
        n_distinct as count
from pg_stats
where tablename  = 'assembly_prods' and
      attname    = 'assembly_id';

The results:

method                      count
count(*) distinct           28088
n_distinct from pg_stats    13805

That's only off by a factor of 2, but I've seem much worse in my data. To the point where I won't use estimates. Is there something else that I can try? Is this something that PG 12 improves?

Follow-up

I hadn't ever experimented SET STATISTICS before, because there are only so many hours in a day. Inspired by Laurenz' answer, I've take a quick look. Here's a useful comment from the documentation:

https://www.postgresql.org/docs/current/planner-stats.html

  The amount of information stored in pg_statistic by ANALYZE, in
  particular the maximum number of entries in the most_common_vals and
  histogram_bounds arrays for each column, can be set on a
  column-by-column basis using the ALTER TABLE SET STATISTICS command,
  or globally by setting the default_statistics_target configuration
  variable. The default limit is presently 100 entries. Raising the
  limit might allow more accurate planner estimates to be made,
  particularly for columns with irregular data distributions, at the
  price of consuming more space in pg_statistic and slightly more time
  to compute the estimates. Conversely, a lower limit might be
  sufficient for columns with simple data distributions.

I have often got tables with a few common values and a lot of rare values. Or the other way around, so the right threshold will depend. For those who haven't used SET STATISTICS, it lets you set the sampling rate as a target number of entries. The default is 100, so 1000 should be higher fidelity. Here's what that looks like:

ALTER TABLE assembly_prods
    ALTER COLUMN assembly_id
    SET STATISTICS 1000;

You can use SET STATISTICS on a table or index. Here's an interesting piece on indexes:

https://akorotkov.github.io/blog/2017/05/31/alter-index-weird/

Note that the current documentation does list SET STATISTICS on indexes.

So I tried out thresholds of 1, 10, 100, 1000, and 10,000 and got these results out of a table with 467,767 rows and 28,088 distinct values:

Target   Estimate  Difference  Missing
     1   13,657    14,431      51%
    10   13,867    14,221      51%
   100   13,759    14,329      51%
 1,000   24,746     3,342      12%
10,000   28,088         0       0%

Obviously you can't draw any general conclusions from one case, but SET STATISTICS looks pretty darn useful and I'll be glad to have it in the back of my mind. I'm tempted to raise the target a bit in general as I suspect it would help in many of the cases in our system.


texts: Tuning queries and indexes using index analysis tool

texts:
I've been pointed to this script from several places which apparently helps with designing indexes in Microsoft SQL Server: http://indexanalysis.codeplex.com/

The problem is I'm using Sybase ASA 11, which seems to use a slightly different implementation of T-SQL than the one used by MS. I'm getting all sorts of errors when I try to run the script.

I know enough about database tuning to tell where to put indexes, and that's about it. I'm a programmer, so I don't know about it as much as I'd like to.

My question is: Is there anything similar to this tool for Sybase? I need to know where to put indexes without going to each and every query (if I can avoid it).


texts: Why does Back Up Database Task fail to connect when Force Protocol Encryption is enabled on MS SQL Server 2019?

texts:
I have MS SQL Server 2019 where encrypted connections have been enabled. A certificate has been issued and installed on the server. In addition, I have also enabled Force Protocol Encryption in SQL Server Configuration Manager for 32-bit and 64-bit clients.
All clients can connect without a single issue.
Problems arise when I try to run a Maintenance Plan with Back Up Database Task.
It errors out with:

Could not load package "Maintenance Plans\MaintenancePlan" because of error 0xC0014062.
Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Client unable to establish connection).
The SQL statement that was issued has failed.
Source:   Started:  0.00.00  Finished: 0.00.00  Elapsed:  0.109 seconds.
The package could not be loaded. The step failed.

In addition, Event Viewer has following error in it.

The certificate received from the remote server does not contain the expected name. It is therefore not possible to determine whether we are connecting to the correct server. The server name we were expecting is . The TLS connection request has failed. The attached data contains the server certificate.

As soon as I disable the aforementioned Force Protocol Encryption in SQL Server Configuration Manager for clients, the Maintenance Plan works. Or if I enable Trust Server Certificate in the same place, the Maintenance Plan works.
I am quite baffled by this issue, because all normal client connections do work even with Force Protocol Encryption enabled and Trust Server Certificate disabled.
Even SQL Server Management Studio works when run locally on the SQL Server, regardless of whether I have enabled Encrypt connection under Connection Properties.
Apparently this is not a common issue, or configuration, because Google search results found no similar cases to mine.
So my my question is, why is it only the Maintenance Plans that are unable to connect and how do I solve the problem without disabling Force Protocol Encryption, or enabling Trust Server Certificate?


texts: Merge replication - Could not remove directory

texts:
We are facing an issue with our replication server. We are currently setting up a new environment to replace the old replication server and we keep getting the following error.

Server XXX\YYY, Level 16, State 1, Procedure sp_MSreplremoveuncdir, Line 83
Could not remove directory '\\\XXXXXX\VTWSnapshot\unc\XXXX$YYYY\77_11\'.
Check the security context of xp_cmdshell and close other processes that may
be accessing the directory. (Source: MSSQLServer, Error number: 20015)

Sometimes when we run the job to generate a new snapshot, it fails and sometimes it succeeds. Since it sometimes succeeds, I'm sure it's not an issue with access or permissions, else it would always fail.

My second thought was that something is locking some of the files, not allowing SQL to delete anything from the folders.
Sadly I only have permission on SQL and not on the server or file share, making this hard to check. Communication also goes a bit slow, me being in Belgium and the head office of their company being in America, that's why I'd like to get as much information as possible so I could ask everything at the same time.

[This may be coincidence, but I'll mention it anyway]

When we run the snapshot job X+1 times, it succeeds, with X being the number of subscriptions we have.

Meaning:
If we have 3 subscriptions A, B and C

Job fails the first time on folder A
Job fails on folder B
Job fails on folder C
Job succeeds

This kind of made me believe that the folders are somehow locked and the lock gets released after the job has run, but failed. The next time the job runs, it can continue with the next folder until they are all unlocked?

[/end of conspiracy]

Any help, thoughts, ideas are welcome.


texts: What does 'fully determines' mean in the context of postgres functional dependency statistics?

texts:
I am following the example of how to use 'create statistics' in postgresql from this page.

CREATE TABLE tbl (
                     col1 int,
                     col2 int
);

INSERT INTO tbl SELECT i/10000, i/100000
FROM generate_series (1,10000000) s(i);

CREATE STATISTICS s1 (dependencies) on col1, col2 from tbl;
ANALYZE tbl;

SELECT stxname, stxkeys, stxdependencies
FROM pg_statistic_ext
WHERE stxname = 's1';

returns:

stxname,stxkeys,stxdependencies
s1,1 2,{"1 => 2": 1.000000}

However, I'm confused by this line

  Looking at this, we can see that Postgres realizes that col1 fully
  determines col2 and therefore has a coefficient of 1 to capture that
  information.

In the example there are many records in col2 that are repeated. So how does col1 'fully determine' col2? What does this mean?


texts:
Sorry for the title, As I'm not that experienced I had a real hard time trying to come up with a proper title.

So I have this query to fetch all the comments of a specific topic.
Now for my paging I want to limit it down to 20 rows, but ONLY for posts with pid (parent_id) zero.

SELECT *, c.id AS cid, u.id AS userid
FROM comments c
INNER JOIN users u ON u.id = c.uid
WHERE c.tid = :id ORDER BY c.posted ASC LIMIT 20

How do I go about limiting the posts by 20 WHERE pid = 0 without removing every row with pid = 1?

If I add:

 WHERE c.tid = :id AND c.pid = 0

I'll just get all comments without a pid.

So, for example, it shows 100 comments, but only 20 that has pid = 0 in the database. That's what I want.


texts:
My pgbouncer.log grows endlessly.

How do I enable filename rollover or limit the logfile size for pgbouncer on Ubuntu?


texts: How can I handle cases where a group of rows have the same identifier in mysql?

texts:
For example, in a log file, I have to collect events in a particular time and I have to store them in the database. How can I do that? The log file looks this:

Example:

Log file 1:

12/12/12 1:20:00.000    Start

12/12/12 1:20:00.000    Initialize

12/12/12 1:21:00.989    Stop

Log file 2:

12/12/12 1:20:00.000     Transmit

12/12/12 1:20:00.000     Hello

12/12/12 1:20:00.099     Receive

12/12/12 1:21:00.989     Bye

If I want the events during 12/12/12 1:20:00.000, I have more than one entry.How can I insert them into the database???


texts: mysqld upgrade 5.5 -> 5.6 mysql Error: Failed while fetching Server version! Could be due to unauthorized access

texts:
After updating mysql with yum to 5.6 I am getting such an error message when trying to run mysql

[root@host rpm]# mysql_upgrade
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Error: Failed while fetching Server version! Could be due to unauthorized access.
FATAL ERROR: Upgrade failed

I also tried

mysql_upgrade -u root -p

and the result is the same.


texts: Repopulating Oracle tables using data from Excel/CSV file

texts:
I'm developing a Java EE application that utilizes Hibernate/JPA.  Some of the data I want to use comes from another Business unit's spreadsheet that I need to periodically (re)load into 5 Oracle tables, overwriting any existing data.  I'm using a shell script that kicks off 5 sql*loader control files, where each control file specifies filler fields to skip because the excel file is not normalized.

OPTIONS(skip=2)
load data
REPLACE
--#########################################
into table TABLE_NAME
 fields terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS
  (
ACCOUNT_ID ,
REST_STOP_ID ,
DESCRIPTION FILLER ,
GROUP FILLER ,
FUNCTION  ,
JOB_ID "substr(:JOB_ID, 0, INSTR(:JOB_ID,' - ',1))",
<35 other fields>
etc...

First time import of this data into empty tables using SQL*Loader and a csv file from this spreadsheet works fine.  But then any subsequent run sql*loader fails because either control file option:

when using REPLACE:

SQL*Loader-926: OCI error while executing delete/truncate (due to REPLACE/TRUNCATE keyword)
**ORA-02292:** integrity constraint (SCHEMA.FK5BA979794B0A176A) violated - child record found

when using TRUNCATE:

SQL*Loader-926: OCI error while executing delete/truncate (due to REPLACE/TRUNCATE keyword)
**ORA-02266:** unique/primary keys in table referenced by enabled foreign keys

I've also tried reordering which the tables are populated but no luck.

I've also tried disabling constraints

ALTER TABLE TABLE_NAME DISABLE CONSTRAINT SYS_C0090398

ORA-02297: cannot disable constraint (SCHEMA.SYS_C0090398) - dependencies exist
(0 rows affected)

My work around so far is to drop all the tables restart my application and have Hibernate recreate my 5 tables and then run my sql*loader script. Right now I'm in development so not a big deal.  But when we go to production, I can't keep restarting and creating tables.  Actually when I go to production, I'll create the tables outside of hibernate but right now I have the persistence option hibernate.hbm2ddl.auto set to update.

I'm looking for an elegant (semi)automatic solution to periodically repopulate these 5 tables with data provided to me in the csv/Excel file.


texts: We'd like to have two MySQL cluster groups, and at the same time have replication set up between them.  Is that possible?


texts:
I have a table with datetime fields start and end. And I have a list of (start, end) items. I need to check which items from the list overlap with data in the table.
The current query looks like this:

select br.duration from booking, (
    select tstzrange('2016-09-06 03:45:00+00', '2016-09-06 14:45:00+00') as duration
    union select tstzrange('2016-09-06 14:45:00+00', '2016-09-06 15:45:00+00') as duration
    -- other items from my list
) as br
where tstzrange(start, end) && br.duration

Are there any other ways to do it? Do you think it will work if I have millions rows in the table and will compare them with hundreds items from the list?


texts: Open Windows Causing Locks After Sproc has Completed

texts:
I had a query that was being blocked. Ran sp_whoisactive @ get_locks=1 and identified the blocking query and it's owner. The result indicated the table had been locked for over four hours. The developer stated it was a stored procedure that she executed and it completed in 21 seconds. She still had the SSMS window open for the stored procedure. As soon as she closed the window the lock was released. I've never seen a completed stored procedure retain the lock simply because the window had not been closed. I've researched, but have been unable to find anything similar to this issue. The stored procedure basically Selects records into a temp table and then UPDATES another table and sets a flag if there is a bad address detected.
Is this standard behavior? Is there a setting that can be changed so this does not happen?
It's SQL Server 2019. SSMS v 18.


texts: tool available to find missing indexes in mssql 2008 ? can we find the missing index using Database Engine Tuning Advisor


texts: How to determine why connection creation lasts so long?

texts: On one of my servers (Windows 2008 Server), creating connection in Java to PostgreSQL server takes from 1,5 to 4 seconds! I have no clue how to start checking what can be wrong. I tried different database on this server, different user. Still the same. If I work with backup of database on my computer, it's 150ms. I would be grateful for any tip where to start.


texts:
I need a script to cycle all tables in postgres to analyze if needed.
Something like:
create or replace PROCEDURE analyze_all_tables ()
language plpgsql
as $$
declare
        q TEXT;
        r RECORD;
  BEGIN
        FOR r IN (SELECT table_name
                  FROM information_schema.tables
                  WHERE table_schema = 'public'
            ) loop
          --  'ANALYZE VERBOSE ',@table,';');
                EXECUTE format('ANALYZE VERBOSE %I ;',
                    r.table_name);
        END LOOP;

end; $$

But filtering only the tables that actually need analyze
UPDATE: I just changed the function to be more postgres compatible


texts: Oracle 12c Release 2 Database Configuration Assistant installation on CentOS - The port is already in use

texts:
I know this error has already been intensively discussed in this thread here: Resolving port 5500 not free issue during Oracle 12c Release 2 Database Configuration Assistant installation on CentOS

However, despite the solutions proposed I can't install Oracle 12c on CentOS 7.

I skiped the Database Configuration Assistant during standard installation process in order to run it again using Xming. There it is possible to enter the EM database express port manually.

I tried almost any port number, I always get the error message shown in the screenshot.

Do you have any suggestions what else I could do?

EDIT:

Content of /etc/hostname:

CentOS-Dev-db-12c.Company.local

Content of /etc/hosts:

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1       localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.10.113 CentOS-Dev-db-12c.Company.local


texts: permissions for yet another "ORA-06512: table or view does not exist" scenario

texts:
See the code below.  I'm running the code with user x in schema y.  The exact same code works if I run it by user x in schema x, but not with user x in schema y.  I get the error ORA-06512: table or view does not exist.  Why would this be?  I'm setting the schema explicitly to y.  When running JUST the create statement with user x in schema y, it runs fine.  And when running JUST the create statement with user x in schema x, it also runs fine.

There are two synonyms in schema y, called aaaaa and bbbbb.  See y.aaaaa and y.bbbbb in the create statement.  Synonym y.aaaaa selects data from table z.aaaaa in schema z.  Synonym y.bbbbb selects data from table z.bbbbb in schema z.  user x currently has SELECT permissions on these two tables in schema z.  I renamed the synonyms (y.aaaaa and y.bbbbb) in schema y so they're easier to find.

The XSQL is the culprit, but not sure what permissions we need.  When XSQL(sql_code) is commented out, it doesn't throw the error.

This doesn't work:

alter session set current_schema=y;

DECLARE sql_code VARCHAR2(4000) :=
'create table basis as '||
'(select my_basecode,c_fullname,encounter_num,concept_cd from aaaaa basis '||
'        inner join enc on enc.patid = basis.patient_num and enc.encounterid = basis.encounter_num '||
'     join bbbbb basiscode  '||
'        on basis.modifier_cd = basiscode.c_basecode '||
'        and basiscode.c_fullname like ''\BASIS\%'') ';
BEGIN
  DROPSQL('DROP TABLE basis');
  XSQL(sql_code);
END;

This works:

alter session set current_schema=y;

create table basis as
(select my_basecode,c_fullname,encounter_num,concept_cd from aaaaa basis
        inner join enc on enc.patid = basis.patient_num and enc.encounterid = basis.encounter_num
     join bbbbb basiscode
        on basis.modifier_cd = basiscode.c_basecode '||
        and basiscode.c_fullname like '\BASIS\%');

For reference purposes:

create or replace PROCEDURE XSQL(sqlstring VARCHAR2) AS
BEGIN
  EXECUTE IMMEDIATE sqlstring;
  dbms_output.put_line(sqlstring);
END XSQL;


texts: Incorrect Estimates (CE Model 130) on uniqueidentifier columns

texts:
We want to change our application from compatibility level 100 to 130. As part of the tests, we came across a curious problem regarding estimates on uniqueidentifier columns.

The select is very simple and should lead to a trivial plan that finds either a step or range hit in the histogram.
It worked wonderfully with the old cardinality estimator, but with the new estimator we get the EQ_ROWS value of the last step instead of the AVG_RANGE_ROW value of the next step when we hit a range. Interestingly, it works correctly in areas of the historgam where sequential GUIDs are present.

Histogram

Following works as expected:

/*STEP MATCH, Estimates 2 Rows (Histogram EQ_ROWS)*/
SELECT count(*) FROM DOR WHERE RefIdGUID IN('3CA17739-7B51-EA11-810F-00155D642227')
OPTION(RECOMPILE);

/*RANGE MATCH, Estimates 1.6666 Rows (Histogram AVG_RANGE_ROWS of the following Step)*/
SELECT count(*) FROM DOR WHERE RefIdGUID IN('3CA1773A-7B51-EA11-810F-00155D642227')
OPTION (RECOMPILE);

And here it goes creepy:

/* STEP MATCH, Estimates 11 Rows (Histogram EQ_ROWS) */
SELECT count(*) FROM DOR WHERE RefIdGUID IN('8BB7B421-BE5F-428C-8A72-03E6B98AA711')
OPTION(RECOMPILE);

/*RANGE MATCH, Estimates 11 Rows!!! But why?
There is no exactly fitting step in the histogram and the AVG_RANGE_ROWS value of the next step shows 1.1 lines and not 11 */
SELECT count(*) FROM DOR WHERE RefIdGUID IN('8BB7B422-BE5F-428C-8A72-03E6B98AA711')
OPTION(RECOMPILE);

/* CE-Model 70, Estimates 1.1 Rows (Histogram AVG_RANGE_ROWS of the following Step) */
SELECT count(*) FROM DOR WHERE RefIdGUID IN('8BB7B422-BE5F-428C-8A72-03E6B98AA711')
OPTION(RECOMPILE,USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'))

Do you have an idea why this is, or do you think this is a bug? (Tested on SQL Server 2016 SP2 and SQL Server 2019 => same result)

STEPS FOR REPRODUCTION

Test Setup
Microsoft SQL Server 2016 (SP2-CU11) (KB4527378) - 13.0.5598.27 (X64)   Nov 27 2019 18:09:22   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Enterprise 10.0  (Build 17763: )

-- set up table
IF OBJECT_ID ('dbo.MyTestTable', 'U') IS NOT NULL
   DROP TABLE MyTestTable;
GO

CREATE TABLE MyTestTable (
    Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    RefIdGUID UNIQUEIDENTIFIER
    )
GO

--create index for test
CREATE NONCLUSTERED INDEX IDX_RefIdGuid ON MyTestTable (RefIDGuid)
GO

-- insert some data
INSERT MyTestTable (RefIdGuid) VALUES (newid());
GO 100

-- insert specific data (50 times the same GUID)
DECLARE @MyGUID_A uniqueidentifier =
(SELECT RefIdGUID
FROM MyTestTable
ORDER BY RefIdGUID
OFFSET (50) ROWS FETCH NEXT (1) ROWS ONLY)

INSERT INTO MyTestTable SELECT @MyGUID_A
GO 50

--update statistics
UPDATE STATISTICS MyTestTable WITH FULLSCAN

Testrun

-- creates a non-existent guid that follows the one with 51 entries
DECLARE @MyGUID_A uniqueidentifier =
(SELECT RefIdGUID
FROM MyTestTable
GROUP BY RefIdGUID HAVING COUNT(*) > 50)
DECLARE @MyGUID_UNDER_TEST uniqueidentifier = (SELECT CAST('FFFFFFFF' + RIGHT(CAST(@MyGUID_A as varchar(max)), 28) as uniqueidentifier))

-- SELECT on non-existing GUID
-- New CE estimate: 51 records
SELECT * FROM MyTestTable WHERE RefIdGUID = @MyGUID_UNDER_TEST
OPTION (RECOMPILE)

-- Old CE estimate: 1 record
SELECT * FROM MyTestTable WHERE RefIdGUID = @MyGUID_UNDER_TEST
OPTION (RECOMPILE, USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'))


texts: PostgreSQL: How to get a sum of values and the total rows that went into each value?

texts:
I have a table, MyData in the following format:

|id|theme_id|priority_id|value|

I am trying to get the following result for all the rows:

theme_id | priority_id | total | sum
------------------------------------
    1    |      1      |   3   |  5
    1    |      2      |   5   |  12
    1    |      3      |   1   |  3
    2    |      1      |   2   |  6

The idea is group all the rows by theme_id and priority_id and then sum up the values for that grouping, as well as, to get the total rows that went into that sum.

When I try the following, I get a total column that equals to the sum column:

WITH breakdown AS (
  SELECT id, theme_id, priority_id, value
  FROM MyData
)
SELECT theme_id, priority_id, sum(value) AS values, count(id) as total
FROM breakdown
GROUP BY theme_id, priority_id

I have also tried:

SELECT theme_id, priority_id, sum(value) AS values, count(id) as total
FROM MyData
GROUP BY theme_id, priority_id

Both of the above give me the same output:

theme_id | priority_id | total | sum
------------------------------------
    1    |      1      |   5   |  5
    1    |      2      |   12  |  12
    1    |      3      |   3   |  3
    2    |      1      |   6   |  6


texts: Insert data from 1 column to many columns in another table (using 1 line) in sql

texts:
I have a table (table1) in which IDs of transactions, transaction state (there are 10 total states) and their time of entering that state are stored.

I want to feed this data into another table which would have 11 columns: 1 for the Transaction ID and the other 10 for the time that they enter each state.

I want to write this as an SQL query. I looked for it and found

insert into table2(ID, s1, s2, s3, s4 ... )
select ID, time from table1
where [];

but I don't have 10 different columns in table1 to copy from as there are 10 different rows corresponding to each ID and each state.

Please help me out in doing this is 1 query.

I am new to SQL. Thanks in advance.


texts: MS SQL Server accepts non-SSL connections even with Force Encryption enabled on the server side

texts:
I am forcing encryption on my SQL Server. My intention is to reject any client connection that does not use SSL to connect. Am I on the right track?

Here are my detailed steps :

makecert -r -pe -n "CN=slc02xla.company.com" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12 c:\my.cer
I imported the same certificate into the trusted Root Certification Authorities Store
In SQL Server Configuration Manager, expandrf SQL Server Network Configuration, right-clicked Protocols for , and then selected Properties.
On the Certificate tab, selected the desired certificate from the Certificate drop-down menu, and then clicked OK.
On the Flags tab, selected Yes in the ForceEncryption box, and then clicked OK to close the dialog box.
Restarted the SQL Server service.

Am I missing anything else?


texts: Trouble Enabling Query Caching on MySQL 5.6?

texts:
Recently I discovered our MySQL 5.6 database is not using any query caching and I wanted to change that.

I added

[mysqld]
query_cache_type = 1
query_cache_size = 50M
query_cache_limit = 2M

to a file called my-default.ini which is located under C:\Program Files\MySQL\MySQL Server 5.6.  I could not find any other .ini file or .cnf file in any directory, and this computer does not have a C:\ProgramData directory where I usually would find it.

Running SHOW VARIABLES LIKE 'have_query_cache' does show have_query_cache    YES.  But SHOW VARIABLES LIKEquery_cache_typeshowsOFF.  AndSHOW STATUS LIKE 'qcache%'` shows all zeros.

Is it possible my my.cnf is missing or deleted?  Do I have to recreate it, or rename the my-default.ini to just my.ini?  I think perhaps a coworker changed the file name or moved it not knowing what it was potentially.

End of the day, how can I ensure that query caching is enabled on my MySQL 5.6 server?

Running it on Windows Server 2003.


texts: Mirror db from production to test and acceptance nightly or weekly

texts:
I have read this Easy or Schedulable way to move data from Production to Test
and it seems not automated. I would like to have a daily or weekly push of my production server data to one or two (remote) test databases.

My current production DB:

  Microsoft SQL Server 2008 (SP3) - 10.0.5520.0 (X64)
      Jul 11 2014 16:11:50
      Copyright (c) 1988-2008 Microsoft Corporation
      Standard Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (VM)

It might get an update to Azure SQL in the future, but it is mssql 2k8 for now.
The current test DB:

  Microsoft SQL Server 2008 R2 (SP2) - 10.50.4033.0 (X64)
      Jul  9 2014 16:04:25
      Copyright (c) Microsoft Corporation
      Express Edition (64-bit) on Windows NT 6.2  (Build 9200: )

I have a lot of sql server jobs from http://ola.hallengren.com and I know about the existence of the import/export wizard, but I'd like to have an easy to setup weekly automated solution to export the data (logs not required) to my test and/or acceptance DB (located on other server)
So I don't think that I need a clustor or failover stuff, just a copy of the data to an other server in an easy automated way.

edit I have read that 2012 gives availability groups, but isn't that an overkill for my situation?

As I already have weekly full backup .bak and daily diff .bak files on my production server, I might just automated copy this somehow an restore. Is this a good idea? https://serverfault.com/a/472007/30695 or is there a microsoft tool with a nice wizard for it?


texts: Why is my newly installed but isolated SQL Server 2016 instance filling disk with transaction log

texts:
This is an entirely new install of SQL server of an entirely new server. The setup is intended to replace another ageing server and we are in the process of setting up the server environment.

Last Thursday we installed SQL Server and restored the clients database, but there are no [nil, none, not any, zero] client users, management jobs, or scheduled processes configured.  Effectively the system is isolated from the world living in its own universe.

On Monday I got a shout from the systems team telling me an out-of-space alert had been raised from the transaction log drive and that it was nearing the top of its 80Gb capacity.

80Gb of transaction log seems like a lot for an untouched SQL database. In a live environment we will have management jobs running to backup the trans log every 15 mins so we will not see the issue.

Obviously something is running to cause SQL to write so much to the trans log. I am curious as to what it might be. Apart from anything else I always assumed that it is user activity and occasional DBA work that causes the translog to grow but it would seem that some part of the growth is due to something else.

Can anyone offer any insight, or suggest how I can analyse the transaction log contents to get a sense of what is happening?

UPDATE: Couple of updates from when I first wrote the question.

Why can't I examine the translog file - it had to be killed to get the space back - not my decision.
There is a VEEEM backup solution in play here. It turns out that the login credentials for VEEEM to access SQL server were not enabled with SQL Server access. The event log shows the VEEEM attempting to log in 4 times them abandoning. The disk space usage graph shows an almost exponential consumption of disk space.


texts: What system privilege is needed for a user to manage user accounts only

texts:
I'm looking to delegate user termination to our NOC.

Requirements are:

A user NOC will be created
User NOC will execute stored procedures allowing them to either 1) lock another db account or 2) Modify a db account.
NOC should not be able to run alter user statements without using the stored procedures.


texts: Query with CASE expressions on a Logger Devices database

texts:
select t.DATETIME,  max(t.Depth) Depth,  max(t.Temperature) Temperature from(
                SELECT CAST(readingDTTM AS DATETIME) DATETIME,
                case Sensor when 'Depth' then lValue end as Depth,
                case Sensor when 'Temperature' then lValue end as Temperature
                FROM loggerData2 WHERE sName = 'Easington' AND
                Sensor in ('Depth','Temperature')
                GROUP BY CAST(readingDTTM AS DATETIME), lValue, Sensor
   )t
group by t.DATETIME ORDER BY t.DATETIME DESC;

The above query shows the following results when querying my database table called loggerData2.
It gets a distinct DATETIME based on both Depth and Temperature (Sensor in the database table).

DATETIME              Depth     Temperature
-------------------   ------    -----------
09/10/2018 10:30:00   48.670    10.35
09/10/2018 10:00:00   48.670    10.35
09/10/2018 09:30:00   48.670    10.35
09/10/2018 09:00:00   48.670    10.35
09/10/2018 08:30:00   48.670    10.35
09/10/2018 08:00:00   48.670    10.35
09/10/2018 07:30:00   48.680    10.35
09/10/2018 07:00:00   48.670    10.35
09/10/2018 06:30:00   48.670    10.35

My problem now is this is only set up for one site at a time.

What I am trying to do is the same as the above query, but this time for only Depth (sensor) but for multiple sites (sName).

DATE TIME             Depth (sitename1)   Depth (sitename2)
-------------------   -----------------   -----------------
09/10/2018 10:30:00   48.670              46.756
09/10/2018 10:00:00   48.670              46.756
09/10/2018 09:30:00   48.670              46.756
09/10/2018 09:00:00   48.670              46.756
09/10/2018 08:30:00   48.670              46.756
09/10/2018 08:00:00   48.670              46.756
09/10/2018 07:30:00   48.680              46.756
09/10/2018 07:00:00   48.670              46.756
09/10/2018 06:30:00   48.670              46.756

Any ideas would be greatly appreciated.


texts: Prevent Firebird's default instance from starting

texts: I want temporarily to use another named instance. Can I prevent the default instance from starting when the PC starts? I am using Windows 8.1


texts: Will our application server have issues connecting to a Postgres 11.4 server with a 9.6 client?

texts:
Our application server is on EC2 running on Amazon Linux 1.  Postgres dropped support for Amazon Linux and so we depend on Amazon providing the postgres client.

The client is 9.6 and our Amazon hosted RDS postgres server is 11.4.  Because of our automation, it would take a good amount of time to upgrade from Amazon Linux 1 to Amazon Linux 2.  Even then Amazon Linux 2 only has a postgres 10 client.

It's a small app that uses the Rails ORM Active Record and only does simple queries and inserts.

Is it a no-go to use 9.6 as the client?  I'm wondering what the risk are.


texts: Simple Query intermittently returning 0 results

texts:
I'm having a strange problem and need help troubleshooting.

if I run this query on my gallery table:

SELECT   *
FROM     `gallery`
WHERE    awarded = 1
ORDER BY awarded_date DESC LIMIT 29 OFFSET 0

it works perfectly, but then randomly return 0 results, for a few seconds and then continues working.

I could be wrong but i think i have narrowed the problem down to the 'awarded' column, as other queries of the same database seem to work without this issue.

the awarded column is an int with value 1 or 0

any thoughts on what could be causing this?

Thanks.


texts: How to avoid Msg 9514, Level 16, State 1, Line 3 error for linked server?

texts:
I have a query to compare data between my local SQL Server and linked SQL Server. My problem is that the table I compare contains XML column. I get:

  Msg 9514, Level 16, State 1, Line 3 Xml data type is not supported in
  distributed queries. Remote object [srv].[DB].dbo.[table]' has xml
  column(s).

I don't know how to fix this, because I don't show this column inside the select statement. I though that I can CAST, but when the column is not in the select list what to cast?

I see that there is workaround with cast by I can not. So may be I miss something.

Any ideas?

My test code looks like:

SELECT
TA2.ID FROM [DB].[dbo].[table1] TA2
  INNER JOIN [linkedsrv].[DB].[dbo].[table1] PA ON (TA2.ID = PA.ID)

I need to update PA:

--UPDATE ALL ON PA

UPDATE XSRV
SET
  XSRV.[Description] = SRV.[Description],
  XSRV.[Code] = SRV.[Code],
  XSRV.[Active] = SRV.[Active],
  XSRV.[Expired] = SRV.[Expired],
  XSRV.[SType] = SRV.[SType],
  XSRV.SupportParameters = CAST(SRV.SupportParameters AS VARCHAR(MAX))
FROM
  [PANI_TEST].dbo.[Services] AS SRV
  INNER JOIN (SELECT X.ID, X.Code, X.[Description], X.Code, X.Active, X.Expired,
      X.SType, CAST(X.XmlParams AS VARCHAR(MAX)) AS XmlParams, X.CreatedDate, X.CreatedBy,
      X.ModifiedDate, X.ModifiedBy FROM [PANI_TEST].[dbo].[Services] AS X) AS XSRV ON
    (SRV.ID = XSRV.ID)
WHERE
  (SRV.[Description]     <> XSRV.[Description] ) OR
  (SRV.[Code]            <> XSRV.[Code]        ) OR
  (SRV.[Active]          <> XSRV.[Active]      ) OR
  (SRV.[Expired]         <> XSRV.[Expired]     ) OR
  (SRV.[SType]           <> XSRV.[SType]       ) OR
  (CAST(SRV.XmlParams AS VARCHAR(MAX)) <> XSRV.XmlParams);

Of course the code above is not working because I use derived table, which is not updatable. I don't figure out right now how to update. If I use simple CAST without derived table I still get error.


texts: Can SQLServer implicitly combine clustered and nonclustered index for queries that hit the NCI with a WHERE and the CI with an ORDERBY?

texts:
I recently asked a question about the best indexing strategy for a table that must record events, in order, against a GUID. The GUID is a nuisance but it's unavoidable because it's an FK to elsewhere.
In terms of the queries I want to do they are essentially of the form SELECT TOP N SomeColumn FROM t WHERE GuidColumn = 'someguid' ORDER BY AutoIncInt DESC
I've done some experimentation and a good tradeoff approach seems to be:

Create the AutoIncInt column on the table as the unique clustering key
Create a nonunique nonclustered index on GuidColumn, and INCLUDE SomeColumn

At first I had the nonclustering index on SomeGuid ASC, AutoIncInt DESC, thinking it would help the ORDER BY AutoIncInt DESC part of the query but the plan remained the same when I removed it - no sort operation was needed and the query just hit the NC index, did a top and a select; it seems that SQLServer is somehow smart enough to know, even when AutoIncInt is not cited in the nonclustering index at all, there's a way to easily get the data out in order / easily calculate which last N rows are needed for the TOP
How does this mechanism work in practice? Does the nonclustering index have an implicit order thanks to referring to the clustering index, such that SQLS can quickly and easily deduce which N index nodes I want the SomeColumn from?


texts: Difference between has and contains in ERD diagram

texts:
I am working on an ERD which represents the relationships between entities in a Web development company behavioral model.
There I have entities like:

Customer
Service ----------- (like: web development, web design, web consultation, ...)
Order-item ------- (like: web development, web design, web consultation, ...)
Order
Invoice
Project
Project-phase
Task --------------- (like: web development task3, web design task7)
Developer
Delivery ----------- (like: the developed web app, web consultation session, ...)

I have this diagram so far, though I know it still has major problems, and it is just for a start point:

Main concept

Customer makes order-items on services, combined to form an order which relates to an invoice.
Each project has multiple project-phases with each project-phase related to an order.
Each project-phase has multiple tasks, each of which is assigned to a developer.
Each project-phase has a delivery, which will be delivered upon payment of the related orders invoice.

Questions

Are has and contains completely the same? If not, how to choose between them for relationships?! what is the measure?!
Are there any rational problems with the Main concept section?
Any helpful ideas or suggestions to improve this model?

Any help with this, would be really appreciated.

Update

Final version of the diagram


texts: Initialize replication sql server from backup of many databases

texts: I want to know how I can initialize a subscription from a backup of a publication database. I have three diferent databases that will point to the same subscriber. For the first time there is no problem but the second and the third one I have problems. How can I initialize the suscription without lossing the data from the first time and the data from the other two db integrate to the first?


texts:
I have a 10 GB .mdf file with 250 mb .LDF.

I want to shrink the 10 GB .mdf (if possible)

I read about the dbcc command, but I read it in context of shrinking the .LDF (into any size desired using truncate).

However I want to shrink the .MDF file without loosing data.

What is the precise command for it ?


texts: search path is getting updated automatically

texts:
In postgres 11.5 for a running session suddenly lot of errors reported in pg_logs "relation does not exist". After enabling extra logging we have found out that just before the errors the following statement is getting executed and feels same is the culprit.
SELECT pg_catalog.set_config('search_path', '', false)

We are not running it. Could anyone please help me to understand by whom and under what circumstances it is getting executed.


texts: Is there a tool that can check the availability of an Oracle 11g database on a remote machine?

texts: I'm configuring the boot sequence on a Linux box, and one of the startup applications is dependent upon an Oracle instance being available on a different box. I would like a bash scriptable tool to check the availability of the Oracle instance. Likely, the power button on both boxes will have been hit within seconds of each other, and I need the longer Oracle process to finish before my application attempts to connect.


texts: Show all options with many-to-many and many-to-one

texts:
I have inherited a database the following relationships:

Product >--< Option --< OptionValue

So, for example, a Desk might have Colour, Width, and Height as Options, with Colour={Oak,Beech,Walnut}, Width={90cm,110cm,150cm} and Height={60cm,75cm,90cm}

This is models as a M:N, 1:N, and is normalised (i.e. Product, ProductOption, Option, and OptionValue all have the expected PKs and FKs).

The requirement is to get all combinations of Option for each Product.

I'm essentially thinking this is an EAV schema in disguse, but I'd be happy for someone to humour me.

Previously, there were a limited number of options per product, so I could use a PIVOT and left joins (and make dbas around the world cry), but the requirements have changed to allow an unlimited number of options per Product.


texts: What is held in memory during the life of a CTE?

texts:
Assuming a multi-read/multi-write CTE, what exactly is held in memory during the life of the statement?

Is all data except RETURNING data discarded at the end of each sub-statement?


texts: Normalization for postal addresses in US (address, county, city, state, zipcode)?

texts:
I've been trying to understand for the last few years which way is good for storing addresses. I've been getting "normalize all the way" but also "denormalize as much as you can" and I just cannot get my head on deciding what is good for my project.

Shortly, my project would involve lots of users (100k+) and all users will have 1-3 addresses stored (personal, business and billing). That means that I can have 100k+ * 3 records for addresses. Also, I will be doing a lot of look-ups by zipcodes (get users that have addresses registered into a zipcode).
I will only have U.S addresses.

I am happy with the user-to-address tables and their relationship for my project. However, the tables without relationships is what drives me nuts.

(My tables displayed in the image are like that just for me to get a better view on what I need and how do to it. I know there are a lot of redundant fields so please don't take them as they are.)

Does anyone has any tips on how should this be designed?
Does anyone has a link or something to a schema or a similar schema of what big companies use (UPS, USPS, etc)?


texts: How to get a working and complete PostgreSQL DB backup and test

texts:
I was hoping I could get a clear answer on how to ensure taking a full Postgres backup just like you would get with MS SQL Server, and then take care of orphaned users.

From what I've read, and it could be wrong, finding a good PostgreSQL blog has been challenging so please feel free to recommend some to me, I need to figure out how this app works so I can have trust in my backups and Slony replication.  I had a developer restore a backup I took from PgadminIII via custom, directory, and tar format while selecting OIDs but he said two of them didn't load, tar did but it was only the directory, not the data.  I'm really confused now.

I am using PGAdminIII, it has a pg_dump and pg_dumpall option.  I want to back everything up that I need to test restore this database somewhere and verify that yes, all the data we need and our backup is good.  Eventually I want to write an auto-restore script but one day at a time.

pg_dumpall apparently has a -globals option that's supposed to backup everything, but the help for pg_dumpall shows a -g, --globals-only          dump only global objects, no databases, not a --globals option.

I thought pg_dumpall would at least backup foreign keys, but even that seems to be an 'option'.  According to the documentation, even with pg_dumpall I need to use a -o option to backup foreign keys, I can't really imagine when I wouldn't want to backup foreign keys and this would make more sense as a default options.

How would I take care of orphaned users and validate I have everything?  I'd like to actually restore my backup file on another server and verify everything works.  If anyone has any suggestions on how to take a real backup in PostgreSQL and restore, I'd be very grateful.

I had a PostgreSQL server but I still can't fathom why the app would not backup OIDs by default!  It seems like 99.9% of the time you would want that.

UPDATE 1:

The Postgres documentation mentions that the globals option I was looking for seems to be a default option on this version, but it still needs the -o option.  If someone can verify or give me an example command to restore a single database elsewhere with everything it needs I'd appreciate it.

Edit: being asked by site to show the uniqueness of this question by editing my question.  This question raises the issue and get's clarity on OIDs in backups, the difference between globals and non globals, as well as testing restores recommendations to ensure the backup is good as opposed to just backing up.  Due to the answers I was able to backup, figure out globals/oids, and started a test restore process nightly on Postgres using cron jobs.  Thanks for the help!


texts: Should I create many ID for a same Person/entity in my DB?

texts:
In my model, I must store the name of the teacher of some kids and the classroom in which the teachers work, let’s say. All of the hundreds of teachers in the schools have 1 and only 1 classroom, except 2 teachers, which have 2 classrooms and there is no way we can remove one classroom (we MUST keep the 2 classrooms)… do you know of a way to handle this exception ? Knowing that it might happen with other teachers in the future.

The only solution I found so far goes as follow:

Kid Table

Kid ID (PK)

Teacher ID

Teacher Table

Teacher ID (PK)

Teacher Name

Classroom ID

And to have 2 teacher ID (or more) for the very few teachers who have more than 1 classroom. I do need the teacher name in the teacher table to be able to produce stats based on the teachers (no matter the number of teacher ID related to any given teacher).

Does it this model make sense ? is there a better way to do it ?


texts: Difference between DEFINE_CHAIN_EVENT_STEP and DEFINE_CHAIN_STEP procedure

texts:
As per oracle help:*

  DEFINE_CHAIN_EVENT_STEP Procedure:
  This procedure adds or replaces a chain step and associates it with an event schedule or an inline event. Once started in a running chain, this step will not complete until the specified event has occurred. Every step in a chain must be defined before the chain can be enabled and used. Defining a step gives it a name and specifies what happens during the step. If a step already exists with this name, the new step will replace the old one.

  DEFINE_CHAIN_STEP Procedure:
  This procedure adds or replaces a chain step and associates it with a program or a nested chain. When the chain step is started, the specified program or chain is run. If a step already exists with the name supplied in the chain_name argument, the new step replaces the old one.
  The chain owner must have EXECUTE privileges on the program or chain associated with the step. Only one program or chain can run during a step.

Though the help documentation is elaborate, unfortunately I'm failing to understand fully. what is the difference between these two procedures?


texts: protecting network from external attack (SQL server 2012 bruteforce)

texts:
My SQL server 2012 log indicates multiple bruteforce attempts to logon to the server.

Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: xxx.xx.xx.xx]

The account has a very secure password, which buys time for now.

It was recommended that I use a trigger to prevent ip logins. However, this is proving ineffective. How do I put an end to this issue.

CREATE TRIGGER IPtrigger
    ON ALL SERVER
    FOR LOGON
AS
BEGIN
    DECLARE @IP Varchar( 500 )
    SET @IP = EVENTDATA().value( '(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(500)' )

    -- set of the restricted IP addresses
    IF @IP IN ( 'xxx.xx.xx.xx' )
    BEGIN
        Print 'IP BANNED FROM LOGON: ' + @IP
        ROLLBACK TRANSACTION
    END
END
GO


texts: created a user with clusterMonitor and dbOwner role, but db.getRoles() shows enableSharding role

texts:
I created a user with full privileges in it's own database (dbOwner) and read-only access to administrative commands (clusterMonitor)

use customerdb
(mongod-3.4.7) customerdb> db.createUser( { user: "customer",
...                  pwd: "customerpw",
...                  roles: [ { role: "clusterMonitor", db: "admin" },
...                           { role: "dbOwner", db: "customerdb" }] },
...                { w: "majority" , wtimeout: 5000 } )
Successfully added user: {
  "user": "customer",
  "roles": [
    {
      "role": "clusterMonitor",
      "db": "admin"
    },
    {
      "role": "dbOwner",
      "db": "customerdb"
    }
  ]
}

Enabled auth and logged in with the new user. It's a Homebrew installed MongoDB single instance with latest release.

$ mongo -u customer -p customerpw localhost --authenticationDatabase=customerdb

Why getRoles() shows me enableSharding role? I didn't found explanation in docs

> db.getRoles(
...     {
...       rolesInfo: 1,
...       showPrivileges:false,
...       showBuiltinRoles: true
...     }
... )
[
  {
    "role": "dbAdmin",
    "db": "customerdb",
    "isBuiltin": true,
    "roles": [ ],
    "inheritedRoles": [ ]
  },
  {
    "role": "dbOwner",
    "db": "customerdb",
    "isBuiltin": true,
    "roles": [ ],
    "inheritedRoles": [ ]
  },
  {
    "role": "enableSharding",
    "db": "customerdb",
    "isBuiltin": true,
    "roles": [ ],
    "inheritedRoles": [ ]
  },
  {
    "role": "read",
    "db": "customerdb",
    "isBuiltin": true,
    "roles": [ ],
    "inheritedRoles": [ ]
  },
  {
    "role": "readWrite",
    "db": "customerdb",
    "isBuiltin": true,
    "roles": [ ],
    "inheritedRoles": [ ]
  },
  {
    "role": "userAdmin",
    "db": "customerdb",
    "isBuiltin": true,
    "roles": [ ],
    "inheritedRoles": [ ]
  }
]

the privleges for enableSharding role

  {
    "role": "enableSharding",
    "db": "customerdb",
    "isBuiltin": true,
    "roles": [ ],
    "inheritedRoles": [ ],
    "privileges": [
      {
        "resource": {
          "db": "",
          "collection": ""
        },
        "actions": [
          "enableSharding"
        ]
      }
    ],
    "inheritedPrivileges": [
      {
        "resource": {
          "db": "",
          "collection": ""
        },
        "actions": [
          "enableSharding"
        ]
      }
    ]
  }

I tested this in a sharded cluster on mongos with version:

MongoDB Enterprise mongos> db.version()
3.2.11

and also on MacBook with single mongod and version 3.4.7

I guess I am doing something wrong how I create users and grant roles?


texts: Rebuilding, truncating etc SQL Server 2008 maintenance

texts:
About a month or so ago I inherited this somewhat-new database. I was lead to believe that everything was set up and all maintenance tasks were running without a hitch. SQL Server 2008 R2

I was looking at it today while trying to troubleshoot some slowness and discovered that we are almost out of space on our ~500gb array. We have multiple tables whose sizes are upwards of 6gb, which I'm not too concerned about. BUT they have corresponding log files that are around 5x the size! One of them is 31gb. Their modification dates are 3 days ago. The little I have read leads me to believe this is incorrect.

My questions-

Can I safely truncate these log files?
How often should I do this, and when?  (The nightly backup is at 12am.)
I see no re-indexing, statistic updating, etc.  Is it safe to go ahead and set these tasks up?  Should I wait until after the aformentioned truncation?
Can the software writing/reading to the database be used while these tasks are being performed?  We are 24 hours and stopping production is very difficult.
We also have a LOT of backup files.  Like, one for every table for every day since February.  Can I delete some of them?  Is there a way to set the backup to do this automatically?

Thanks so much guys.  We have an IT department but I'd rather not have to wait on them, especially if these things are easy to do.  I'd rather set them up myself (if possible) and let a DBA check in on them occasionally.


texts: Having both a integer Primary Key and a GUID/UUID column on same table-is this bad

texts:
I have my MySQL database having tables where on each table, we have a ID (int, auto increment) clustered primary key and a UUID (varchar36) column that's just a GUID.

The ID is there just for indexing purposes and nothing more.

Joins and all queries will run against the UUID column. That means foreign keys will reference the UUID columns.

Reason I do this is because I want to be able to migrate more easily.
I am aware of the size penalty of the GUID.

Question: is this bad architecture? What are the performance implications beyond size?


texts: Determining maximum table size, type of engine and type of indexes supported

texts:
How many types of indexes are available with SQL Server 2005 and SQL Server 2008? MySQL has a variety of options like: Hashed, BTree, etc. What are the choices in SQL Server?

I also want to determine the maximum table size supported and consumed and the type of storage engine SQL Server is using.

Please explain both, 2005 and 2008 professional and express editions.


texts:
I don't have that much knowledge about mysql and i would like to secure a MySQL server.

We have a few VPS running around the world (6 currently) and  they all need to store information in our mySQL server.

Each VPS will run 1 to 3 scripts and each script is storing 100 rows in our main table.
With our estimation our main table will contain 20 000 000 rows and then will get 10k to 20k rows a day.

Currently I was thinking about allowing those 6 vpn to connect to our mysql server and a different username/password for each script. I was also thinking of limiting the rights to insert only for the script.

What do you guys think? Are my plans on the right directions? Any other advice?


texts: Soft delete field causes left join not to return my "left" side data

texts:
I've got a parent table of trainings and a child table of trainingattendees.
That is a training can be attended by multiple attendees.
I have a bit field in my trainingattendee table called Deleted that has a default value of (0).  That is the record is not deleted.  This value can change to 1 if the record is deleted.

I was simply doing a left join from trainings to trainingattendees to get some data but if ALL my training attendees table rows are marked as deleted (1) I don't get my training record (the left side table)

That is this works:

SELECT
    Training.TrainingID AS TrainingID
FROM
    Training
LEFT OUTER JOIN
    TrainingAttendee ta
ON
    ta.TrainingID=Training.TrainingID
WHERE
    LOWER(Training.Title) = LOWER('Courtesy Dust Mask Training')
    AND DATEADD(d, DATEDIFF(d, 0, Training.StartDate), 0) >= DATEADD(d, DATEDIFF(d, 0, GetDate()), 0)
    AND (ta.TrainingAttendeeStatusID=4)
GROUP BY
    Training.TrainingID

The issue here is this works because I am not filtering out my training attendee records for deleted ones.  I have created a record in my training attendee table but marked it as deleted and reran this same query with one additional where condition.

SELECT
    Training.TrainingID AS TrainingID
FROM
    Training
LEFT OUTER JOIN
    TrainingAttendee ta
ON
    ta.TrainingID=Training.TrainingID
WHERE
    LOWER(Training.Title) = LOWER('Courtesy Dust Mask Training')
    AND DATEADD(d, DATEDIFF(d, 0, Training.StartDate), 0) >= DATEADD(d, DATEDIFF(d, 0, GetDate()), 0)
    AND (ta.Deleted=0)
    AND (ta.TrainingAttendeeStatusID=4)
GROUP BY
    Training.TrainingID

By adding this: AND (ta.Deleted=0)

I then thought I could change this condition to this:

AND (ta.Deleted=0 OR ta.Deleted IS NULL)

But this too did not work...How can I properly do this such that I get my "left" table data ?


texts: Would appreciate some suggestions on how I should model this data

texts:
I am setting up a website that will have users. Users can search for songs and (if signed in) save them to a playlist or their library of songs.

I want it to be like iTunes in that all the songs on the the playlists are part of the library so when the user choses to view their library of songs, the songs on the playlists are shown as well as the songs just added to the library. I'd like a song to be able to be stored in multiple playlists.

Right now I am using Mongodb, doing something like such:

var UserSchema = new Schema();

var Library = new Schema({
    songs: [SongsSchema],
    user: Schema.ObjectId
});

var Playlist = new Schema({
title: String,
description: String,
user: Schema.ObjectId
});

var SongsSchema = new Schema({
position: Number,
name: String,
artist: String,
    artistGid: String,
album: String,
    albumGid: String
time: Number,
url: String,
    gid: String,
    location: String (either youtube, vimeo, soundcloud for now),
    playlist: [Schema.ObjectId] (array of object ids that point to playlists?)
});

Any other ideas?

edit: More details:

So what I have is a user, library, playlist, and song model.

Essentially I am trying to model something like iTunes.

A user has a library and the library belongs to that user.
A user can have several playlists and the playlist belongs to the user.
A song can be in many different user libraries and many different playlists that belong to different users or the same user (perhaps even on the same playlist multiple times?).

When a user visits a page they can add songs to a playlist and will also see a list of their current playlists and their library. If they click the playlist it will show all the songs on that playlist if they click library it will show all the songs in their library( so like iTunes all the songs on all the playlists and ones just added to the library).

Not sure of the best way to model this.


texts: How should row-specific metadata be created handled for an outer join view?

texts:
I have two tables, a large (millions) table of Tickets for orders, and a small (hundreds of thousands) table for Invoices metadata on a subset of those tickets. The metadata is information about when a ticket was paid and is only created when a ticket is first paid.

I'm currently using a view to present all of this data; a LEFT OUTER JOIN shows all rows from Tickets and associated Invoices data if it's there.

My problem is how do I handle creating new Invoices metadata? I can't update the view directly for new entries, so I have a stored procedure that takes a ticket number and creates or updates the Invoices table, however it only does one ticket at a time; I could be updating hundreds of tickets, so this doesn't work.

There's also no simple select criteria I can use; a specific set of potentially non-sequential tickets may be entered. I can't input a range of tickets, I need an explicit WHERE ticketno IN (Ticket#1, Ticket#2) sort of statement to update only the correct rows.

What is the best way to this sort of insert for related data? I've seen some implimentations of stored procedures taking in a list of values as a parameter, but all of them look very hacky and risky.


texts: SQL Server logs "blob data", viewable with journalctl

texts:
What does it mean when I run journalctl -u mssql-server.service and it says B blob data,

Sep 06 17:34:44 x230 systemd[1]: Stopping Microsoft SQL Server Database Engine...
Sep 06 17:34:44 x230 sqlservr[22239]: [200B blob data]
Sep 06 17:34:44 x230 sqlservr[22239]: [190B blob data]
Sep 06 17:34:51 x230 sqlservr[22239]: [73B blob data]
Sep 06 17:34:51 x230 sqlservr[22239]: [146B blob data]
Sep 06 17:39:51 x230 sqlservr[22239]: [73B blob data]
Sep 06 17:39:51 x230 sqlservr[22239]: [146B blob data]

I'm not running DDL commands either, nor inserting or updating blob data. But it is possible something like mssql-cli is doing that in the background.


texts: How to use value return by postgresSQL function in another query

texts: I am calling PostgreSQL function that returns me the text, How can I store this value in local variable and use the value/variable in another query. ?


texts: Get rows above and below (neighbouring rows) a certain row, based on two criteria SQL

texts:
Say I have a table like so:
+---+-------+------+---------------------+
|id | level |score |      timestamp      |
+---+-------+------+---------------------+
| 4 |   1   |  70  | 2021-01-14 21:50:38 |
| 3 |   1   |  90  | 2021-01-12 15:38:0  |
| 1 |   1   |  20  | 2021-01-14 13:10:12 |
| 5 |   1   |  50  | 2021-01-13 12:32:11 |
| 7 |   1   |  50  | 2021-01-14 17:15:20 |
| 8 |   1   |  55  | 2021-01-14 09:20:00 |
| 10|   2   |  99  | 2021-01-15 10:50:38 |
| 2 |   1   |  45  | 2021-01-15 10:50:38 |
+---+-------+------+---------------------+

What I want to do is show 5 of these rows in a table (in html), with a certain row (e.g. where id=5) in the middle and have the two rows above and below it (in the correct order). Also where level=1. This will be like a score board but only showing the user's score with the two above and two below.
So because scores can be the same, the timestamp column will also need to be used - so if two scores are equal, then the first person to get the score is shown above the other person.
E.g. say the user is id=5, I want to show
+---+-------+------+---------------------+
|id | level |score |      timestamp      |
+---+-------+------+---------------------+
| 4 |   1   |  70  | 2021-01-14 21:50:38 |
| 8 |   1   |  55  | 2021-01-14 09:20:00 |
| 5 |   1   |  50  | 2021-01-13 12:32:11 |
| 7 |   1   |  50  | 2021-01-14 17:15:20 |
| 2 |   1   |  45  | 2021-01-15 10:50:38 |
| 1 |   1   |  20  | 2021-01-14 13:10:12 |
+---+-------+------+---------------------+

Note that id=7 is below id=5
I am wondering does anyone know a way of doing this?
I have tried this below but it is not outputting what I need (it is outputting where level_id=2 and id=5, and the other rows are not in order)
((SELECT b.* FROM table a JOIN table b ON b.score > a.score OR (b.score = a.score AND b.timestamp < a.timestamp)
  WHERE a.level_id = 1 AND a.id = 5 ORDER BY score ASC, timestamp DESC LIMIT 3)
 UNION ALL
 (SELECT b.* FROM table a JOIN table b ON b.score < a.score OR (b.score = a.score AND b.timestamp > a.timestamp)
  WHERE a.level_id = 1 AND a.id = 5 ORDER BY score DESC, timestamp ASC LIMIT 2))
order by score

If it is easier to output all rows in the table, say where level = 1, so it is a full score board.. and then do the getting a certain row and two above and below it using PHP I'd also like to know please :) ! (possibly thinking this may keep the SQL simpler)?


texts: How can I join two tables of an Oracle database containing all rows (not the intersection)

texts:
I have a Oracle database with a table t1 with:

cod | text1
-----------
1   | aaa
2   | bbb
3   | ccc

table t2 with:

cod | text2
-----------
2   | abc
4   | mlc
5   | asc

how can I optain something like:

cod | text1 | text2
-------------------
1   | aaa   |
2   | bbb   | abc
3   | ccc   |
4   |       | mlc
5   |       | asc

If I try to do:

select * from t1
inner join t2 on t1.cod = t2.cod

doesn't works, it gives me only the intersection or something.


texts:
I'm looking for a way to use pg_restore to restore a newly-created database from a dump file, but only certain tables from that file. (There are a lot of extra tables in the database that are slow to restore and that I don't care about.)

I first tried pg_restore with the -t/--table flag, but it didn't enable the extensions I need for those tables. Not a big deal; I can manually run psql -c "CREATE EXTENSION ..." before the pg_restore command. My bigger issue is that the -t command seems to skip other things related to the tables, like constraints and indexes.

Indexes are annoying but I could run pg_restore --list and use awk/grep/etc. to get a list of indexes and pass them in to pg_restore with the -I/--index flag, so the process would be (I think):

pg_restore -t table1 -t table2 --schema-only ...
pg_restore -I index1 -I index2 ...
pg_restore -t table1 -t table2 --data-only ...

But (a) that still won't set up the constraints for me, and (b) it feels like I'm going down a rabbit hole with this roundabout solution for something I thought would be straightforward.

I know you can tell pg_dump to only dump certain tables, but I was hoping to avoid that due to the long time it takes to make dumps of my database.


texts: Oracle: create index on json, Json item lenght is too long long?

texts:
I trying to create a index a clob json field. Is anyone know what could be the limit. I have up to 200,000 char. length json record.

Thanks for any tips.

CREATE INDEX emul_full_idx ON CDP_emultimedia (JsonData)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('section group CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)')
SQL> /
CREATE INDEX emul_full_idx ON CDP_emultimedia (JsonData)

ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50850: ORA-06515: PL/SQL: unhandled exception JSON item length is too long
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366