texts: Best approach for a one-to-many list - how to store/acces some related count info?

texts:
I have a table with persons, and other with interactions, notes and tasks, all related to person(s).
I need to have quick access to:

last interaction date.
number of interactions in the past 1,6, 12 months.
number of notes (Total)
number of tasks

All this info are used for sorting highlighting in a list/table. When user select a person, all related info can be requested from DB, question is how to get this data for all persons from db?
Version 1:
Sub-query and add the info for each person in table.
Could be intensive when tables grows.
Version 2: store this info in person table, update them when new info is created/modified.
This violates the 'single source of true' principle and could be prone to errors.
Any ideas?


texts: How I would I break a total count query into intervals?

texts:
For example, if I did a normal count query like:

select status, count(*)
from customers
group by status

and get the result

status   count
-----------------
open     1200
closed   1002

but I want the count to be split into intervals of 500 so I would get a result returned like this

status   count
----------------
open     500
open     500
open     200
closed   500
closed   500
closed   2


texts: Postgres - how to investigate a dependency before DROPing a field

texts:
I want to drop a field from a partitioned table, POSTGRES claims there are dependencies and proposes using CASCADE. How can I check that nothing important will be deleted before risking DROP with CASCADE?

My table is defined as (edited for brevity) -

CREATE TABLE public.positionfix
(
    fixid3 integer,
    fixid bigint NOT NULL DEFAULT nextval('positionfix_fixid_seq'::regclass),
    messageid bigint NOT NULL,
    fixtime timestamp with out timezone NOT NULL
) PARTITION BY RANGE (fixtime)
WITH (
    OIDS = FALSE
)
TABLESPACE fastspace;

I'm trying to remove the field 'fixId3' using the command -

ALTER TABLE positionfix  DROP COLUMN fixId3;

The error messages I get are -

ERROR:  cannot drop table positionfix column fixid3 because other objects depend on it
DETAIL:  default for table positionfix column fixid depends on sequence positionfix_fixid_seq
default for table positionfix_201909 column fixid depends on sequence positionfix_fixid_seq
default for table positionfix_20190926 column fixid depends on sequence positionfix_fixid_seq
default for table positionfix_20190927 column fixid depends on sequence positionfix_fixid_seq
default for table positionfix_20190928 column fixid depends on sequence positionfix_fixid_seq
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
SQL state: 2BP01

All the tables mentioned in the errors are partitions of the parent table. The issue is that the error message mentions other objects dependent on "fixId3", but the detail messages refer to a different field "fixId"

There should be nothing that's dependent on "fixId3".

My questions are

1) Based on the messages above, which additional objects will postgres delete if I attempt to drop "fixId3" with the cascade option? Field "fixId"? the sequence? both? Something else?

2) why is postgres listing a dependency between "fixId" and the sequence  "positionfix_fixid_seq" when the field being deleted is "fixId3"?

3) how do I remove "fixId3" without risking accidentally removing "fixId" instead. (I am reluctant to try CASCADE before I understand the consequences.


texts: When we boot SQL Server, SAN drives might not be availiable yet. Is there a way to delay SQL Server start by 1 minute? Right now we have to restart SQL Server after we boot the server.


texts:
I have a tabular cube on an SQL Server 2017. The cube is refreshed with a full refresh command in a Server Agent job. The job runs on a schedule.

When I check the history of the job I see that it went through successfully, so everything is "green".

When I check the tables of the cube one of the tables is not processed well, it does not contain any data. Now with a manual processing of only this table the problem is solved. So it is not a table config/definition issue.

Where can I find more information about error message why this table is not processed in the automated full refresh command?


texts: AWS RDS Multi A-Z vs DigitalOcean Managed Databases

texts:
I am currently hosting my database inside a t2.large instance on AWS EC2 which provides 8GB RAM and 2 CPUs. I am now looking for better options which are more scalable and provides features like master-slave replication, automatic backups, etc.

I have found AWS RDS Multi A-Z and DigitalOcean Managed Databases to be quite similar in what they offer however the pricing is quite different. Any suggestions on which one to prefer? Are there any other reasonable options?
My requirements are 32GB RAM and 500GB of disk space which should be expandable without any downtime.

Also any disadvantages of using AWS RDS Reserved Instance over On-demand instance? Can one increase disk space and RAM in Reserved Instance without any downtime?


texts: performance: {ts 'datetime'} vs DATEADD()

texts:
I received a claim for an incredible slow query in our print label software. With SQL Profiler I got the offender query and started to test in SSMS with really bad performance (around 00:50:00).
The query is long, and the tables are big, but there is a

WHERE
 ...
 fieldX > {ts '2016-01-01 00:00:00} /* fieldX is a DATETIME */
 ...

I changed to

WHERE
 ...
 fieldX > DATEADD(YYYY, -1, GETDATE())
 ...

And the execution time now shows 00:00:00 !!

I just want understand why?


texts: Column with Default value as Sum of TIMESTAMP

texts:
I have a table which has 2 columns ( JobDate , RecordTime)

--------------------------------------
JobDate       | RecordTime |   SumCol
--------------------------------------
2019-07-20    | 2019-07-21 |
2019-07-19    | 2019-07-20 |

I need SumCol to have a default value as UNIX_TIMESTAMP(JobDate) + UNIX_TIMESTAMP(RecordTime)

I've tried creating a virtual column but it gives me this error :

Expression of generated column 'Test2' contains a disallowed function.

that's what I tried:

ALTER TABLE `jobsTemp`
ADD  `SumCol` TIMESTAMP
AS (UNIx_timestamp(`JobDate`) + UNIx_timestamp(`RecordTime`));


texts: What is the difference between `ALTER TABLE DISABLE TRIGGER` and `DISABLE TRIGGER`?

texts:
My company logs DDL on one database using a Database Trigger, looking for DDL including:

ALTER_TABLE
CREATE_TRIGGER
ALTER_TRIGGER
DROP_TRIGGER

When I disable or enable triggers using ALTER TABLE x DISABLE TRIGGER tiud_x, this change is logged with the Database Trigger.
When I disable or enable triggers using DISABLE TRIGGER tiud_x ON x, this change is not logged.
To me, DISABLE TRIGGER and ENABLE TRIGGER look like "one weird trick" to avoid firing a database trigger, since they seem to be functionally equivalent. What's the difference between ALTER TABLE DISABLE TRIGGER and DISABLE TRIGGER?


texts: How to sort rows to appear alternating (e.g. A B A B ... )?

texts:
Suppose I have a column x with values A and B. For example:

Row X
1   A
2   A
3   A
4   B
5   B
6   B

Now, I want to sort them into this fashion:

Row X
1   A
2   B
3   A
4   B
5   A
6   B

Of course, I can create a new Column Y. Then assign A rows to odd number (1, 3, 5, ...), and B rows to even number (2, 4, 6, ...), and then sort with the Y column. But is there an easier way to achieve this?

It will be best if the answer can work with MS SQL.


texts:
I have a SQL 2016 instance installed. It has SSISDB for SQL server integration services installed.
Now because it is taking up all the space on our system drive, I need to move its data and log files to a separate disk. To do that I will need to take the db offline.
Is there any repercussions you guys can think of after taking it offline. I have coordinated a time with application guys already.


texts: Postgresql database NOTIFICATION trigger that only returns if some sql is met

texts:
I'm trying to get a postgresql trigger that calls a notify function to work but only if the data meets certain criterion.
create trigger notify_site after insert on sites
for each row execute procedure notify_site();

So far I've got it triggering OK on every row thats insterted into the table using the following trigger.
Which is great.. but what I need to do now is only have it call the pg_notify if site='x' and not if site is something else..
How do I go about that (trigger newbie here, but a google hasn't helped so far).
For bonus points... how would I insert the tablename into the json object being returned by the select all?
Thanks to @a_horse_with_no_name I've managed to get the trigger working selectively quite happily.
Adding the table name to the json on the other hand, not so much.
I've managed to get the function to work without the table name addition, and have managed to get my attempt to actually run without errors, but it produces no output either.
This is what I've come up with
create or replace function public.notify_site()
returns trigger
language plpgsql
as $function$
declare
  payload jsonb := row_to_json(NEW)::text;
begin
payload := jsonb_build_object('tableName', TG_RELNAME) || payload;
perform pg_notify('ppsite', payload::text);
      RETURN new;
    END;
$function$;

But with this code, nothing ever makes it into the queue.
This code works fine, but without the table name
create or replace function public.notify_site()
returns trigger
language plpgsql
as $function$
begin
perform pg_notify('ppdsite', row_to_json(NEW)::text);
      RETURN new;
    END;
$function$;

Any thoughts.
Further on from this thread... I tried the following, which actually ran, but instead of adding the table name to the return, it removed the first two fields entirely from the package sent to the notification...
create or replace function public.notify_site()
returns trigger
language plpgsql
as $function$
declare
  payload jsonb := row_to_json(NEW)::text;
begin
payload := json_build_object('tableName', TG_RELNAME)::jsonb || payload;
perform pg_notify('ppsite', payload::text);
      RETURN new;
    END;
$function$;

The same thing happened if I tried
payload := payload || json_build_object('tableName', TG_RELNAME)::jsonb


texts: sum of double precision gives weird results

texts:
I've been making a sum of a column containing about 500.000 records of double precision numbers. All numbers in the database should normally be of two ciphers behind the period. However when making the sum I get 6 numbers after the period: 123123123.549977

Either I have bad data in my database where there are records with more numbers after the period or there is something I'm missing about the sum function.

So my questions are:

Does the sum function have any rounding properties which may cause this?
Is there a way to select all records containing more than two numbers after the period?


texts: How to update array fields in a table in Postgresql?

texts:
Say I have a table defined as following:

CREATE TABLE my_tbl (
    id  bigint,
    a1  bigint[],
    a2  bigint[]
);

I would like to create a stored procedure to append values to a1 and a2. Is this the right way to proceed or is there a simpler way?

CREATE OR REPLACE FUNCTION append(
        iid  bigint,
        next_a1  bigint,
        next_a2  bigint)
DECLARE
    r       "my_tbl";
    tmp_a1  bigint[];
    tmp_a2  bigint[];
BEGIN
    FOR r IN SELECT * FROM "my_tbl"
              WHERE r."id" = iid
                FOR UPDATE
        LOOP
            tmp_a1 := r.a1 || next_a1;
            tmp_a2 := r.a2 || next_a2;
            UPDATE my_tbl SET ( "a1", "a2" ) = ( tmp_a1, tmp_a2 )
             WHERE "id" = iid;
        END LOOP;
END; $$
LANGUAGE plpgsql;

I am on Postgresql 9.2.


texts: How can I determine who dropped a DB in SQL Server 2005 (and when)?

texts: Is there a way to find out who dropped a database in SQL Server 2005 and the time / date that it was dropped?


texts:
I have a main server(windows-27017) and on that i replicated my data on different port (37017) but same server. Now i am trying to add my notebook as a replicate server on mongodb and installed mongodb services on it on port 27017. Now i have three server one main and two replicate. The main server and the port server is working perfectly but the the notebook server is not working. I checked status of the main server and found this

Wed Oct 30 12:25:16.278 trying reconnect to 127.0.0.1:27017
Wed Oct 30 12:25:16.279 reconnect 127.0.0.1:27017 ok
rs1:PRIMARY> rs.status()
{
        "set" : "rs1",
        "date" : ISODate("2013-10-30T03:25:25Z"),
        "myState" : 1,
        "members" : [
                {
                        "_id" : 0,
                        "name" : "192.168.10.99:27017",
                        "health" : 1,
                        "state" : 1,
                        "stateStr" : "PRIMARY",
                        "uptime" : 6832,
                        "optime" : Timestamp(1383103516, 1),
                        "optimeDate" : ISODate("2013-10-30T03:25:16Z"),
                        "self" : true
                },
                {
                        "_id" : 1,
                        "name" : "192.168.10.99:37017",
                        "health" : 1,
                        "state" : 2,
                        "stateStr" : "SECONDARY",
                        "uptime" : 9,
                        "optime" : Timestamp(1383103516, 1),
                        "optimeDate" : ISODate("2013-10-30T03:25:16Z"),
                        "lastHeartbeat" : ISODate("2013-10-30T03:25:24Z"),
                        "lastHeartbeatRecv" : ISODate("2013-10-30T03:25:23Z"),
                        "pingMs" : 0,
                        "lastHeartbeatMessage" : "syncing to: 192.168.10.99:2701
7",
                        "syncingTo" : "192.168.10.99:27017"
                },
                {
                        "_id" : 3,
                        "name" : "192.168.10.97:27017",
                        "health" : 1,
                        "state" : 2,
                        "stateStr" : "SECONDARY",
                        "uptime" : 9,
                        "optime" : Timestamp(1383099655, 1),
                        "optimeDate" : ISODate("2013-10-30T02:20:55Z"),
                        "lastHeartbeat" : ISODate("2013-10-30T03:25:24Z"),
                        "lastHeartbeatRecv" : ISODate("1970-01-01T00:00:00Z"),
                        "pingMs" : 1,
                        "syncingTo" : "192.168.10.99:27017"
                }
        ],
        "ok" : 1
}

I wrote a C program and when i am trying to send the data..it is only being sent to main server and and the port server but no to the notebook server. Can anyone please tell me how i can access notebook server from the main server


texts: Using a SSL-cert from a trusted CA offline to comunicate with SQL Server

texts:
I'm setting up a secure link between client machines and an SQL database server on a intranet without Internet-access.

The question I have is if I can buy and use a cert from a trusted CA on a local offline intranet without getting any message boxes about things like being unable to verify the validity of the certificate, if it has been revoked etc?

If not, then maybee it's just as good to have a self-signed CA (where I'm sure to get a message about giving trust to a unknown CA when installing the cert...)


texts: Can I Upgrade SQL Server 2008 R2 Express to SQL Server 2014 Developer edition

texts:
I currently have SQL server 2008 R2 Express installed for my QA/Dev environment on Amazon EC2 instance.

Now my database is growing beyond the 10GB limitation of Express edition, so am planning to buy SQL server 2014 Developer edition.

So can i upgrade my SQL Express 2008 R2 to SQL server 2014 Developer Edition, if yes what is the procedure?


texts: WinSCP extract won't connect when run by SQL Server Agent Job

texts:
Situation
We use WinSCP to receive daily csv files from a vendor which we then extract and load into our data warehouse.  To achieve this, we have a SQL Agent scheduled job that executes a process task that kicks off a batch file. This batch file then runs a FTP script that downloads the folders from WinSCP to a local drive which later goes through the ETL process.

Complication
Recently our vendor updated their FTP site.  We were required to update the session in WinSCP.  I updated the session and can log into the WinSCP session manually (by double-clicking .bat file from a local box) but when SQL server agent kicks off the batch process via executing process task I receive the following error.

Network error: Connection to "ftp.hostsite.com" timed out
The server rejected the SFTP connection, but it listens for FTP connections.
Did you want to use the FTP protocol instead of SFTP? Prefer using encryption.

Batch File

"\\sharedfolder\winscp.exe" compdata /console /script=\\sharedFolder\Data\App\DownloadFlatFile.ftp

DownloadFlatFile.ftp

get /home/*.csv \\sharedfile\Data\
mv /home/*.csv  \\sharedfile\Data\
exit```

Here is WinSCP session info.
File protocol: SFTP
Hostname: ftp.site.com
Port number: 22
Username: privateusername
Password: none

Then under advanced settings >> SSH >> Authentication I have the path to the ppk file


texts: How to only pick the first matched row in a one-to-many relationship?

texts:
I have two tables A and B with one-to-many relationship.

For rows A1 and A2 in table A, the relationship is

A1 -> [B11, B12, B13]
A2 -> [B21, B22]

When I join A and B I get

A1,B11
A1,B12
A1,B13
A2,B21
A2,B22

Instead I want

A1,B11
A2,B21

What should I do? I don't know what this mapping is called.


texts: error code 3417 - after installing CU for SQL SERVER 2016 SP1

texts:
I am getting below error message after installing cumulative updates for SQL Server 2016 Sp1.

  The description for Event ID 7024 from source Service Control Manager
  cannot be found. Either the component that raises this event is not
  installed on your local computer or the installation is corrupted. You
  can install or repair the component on the local computer.

  If the event originated on another computer, the display information
  had to be saved with the event.

  The following information was included with the event:

  SQL Server (MY_INSTANCE_DEVELOPMENT) %%3417

  The locale specific resource for the desired message is not present

I have 2 instances on that server,
DBA16 AND DEVELOPMENT.

DBA16 started up nicely and no problem, but DEVELOPMENT is not starting.

I tried to start the service manually and get the same problem.

I have applied these same updates on several servers, including clustered ones, with alwaysOn and replication, and had no problems.

Replication distributor also worked fine.

This server in particular, I could find no reason for these problems.

I might have to re-install sql server or rebuild the instance, if it is related to corruption in some file, or the master database.

is there anything I should be aware of or might have not looked\considered?

There is a similar question here: Start service error code 3417 with SQL Server 2012

Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 15173, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.


texts: Oracle SQL Loader - load file where record is composed of "n" delimiters

texts:
How would you write a control file for Oracle SQL Loader that treats each record as being composed of 3 commas?

I have read the documentation, however I cannot find how to handle this scenario, without having to edit my data file by hand.

For example, my data file has:

fd,3232,gfd67gf,
peas,989767,jkdfnfgjhf,
dhdhjsk,267,ujfdsy,fuyds,637296,ldosi,fduy,
873,fuisouyd,try
save,2837,ipoi

I would like to load the data into the table looking like this:

col1       col2       col3

fd         3232       gfd67gf
peas       989767     jkdfnfgjhf
dhdhjsk    267        ujfdsy
fuyds      637296     ldosi
fduy       873        fuisouyd
try\nsave  2837       ipoi

Note: Where I have "\n" in the last row of col1, I mean an actual line feed not literally "\n".


texts: High Availability with Transactional Replication

texts:
We are using SQL 2017 Enterprise and setting up a new DR site. One of our servers (Server B) is used for reporting and has many databases, one of which has tables populated through Transactional Replication from a different database on a different server (Server A). The Publisher is on Server A. The Distributer and Subscriber databases are both on Server B.

Server A is part of a cluster with an Availability Group which includes a DR server. Server B isn't part of a cluster, but I need a DR solution that allows for a failover if we lose a server or our entire primary site.

I'm planning to make a new cluster with Server B and a duplicate for DR (Server B2), but would like to know the best way to deal with the Distributer and Subscriber databases. Can they be part of an Always On Availability Group and will the Transactional Replication continue if there's a failover?

Are there any recommendations from anyone who has experience in this area?


texts: Why is IDENTITY_INSERT ON only allowed on one table at a time?

texts:
It is the case that IDENTITY_INSERT can only be set to ON in one database table at a time, but why? Since IDENTITY columns aren't globally unique I can't think of any dangerous situation that could be caused by inserting identities into more than one table at the same time (at least not more dangerous than generally fudging with IDENTITY INSERT).

IDENTITY INSERT should rarely be used but what is the reason for the hard limit?


texts: Does commit after a normal select can call any fsync or flush operations?

texts:
I'm using Mariadb 10.2.16. 3 Node cluster.
Coms counts taken at particular time for all 3 (insert,update,commit).
Com_inserts are in 1.1K
Com_updates are in 1.6k
Com_commit are in  10K

When enabling general log, I could see after each selects there is a commit. Would this unnecessary commit can cause any fsync or any flush opertation to the server?
innodb_flush_log_at_txn_commit=0.


texts:
I created a temp table which resides in the temp table folder. The problem is the name is unacceptably long which make it almost impossible to efficiently work on querying some field from it. Is it any way to have a shorter name like db.#SPR1 instead of the long one as below?

dbo.#SPR1_______________________________________________________________________________________________________________000000003A3E


texts:
Does anybody know of a way to alter a time zone on a SQL database?

Is there any tool that could find the objects using the date and time?


texts: SequelizeDatabaseError While Upserting, Wrong Table Being Referenced

texts:
I'm selecting from a table, and the query runs fine. It even shows up on the client side without a hitch.

`
SELECT * FROM public."roleAllocation"
`

However, when I run an upsert function from a query that pulls data from other tables in the db, it throws a Sequelize error.

 SequelizeDatabaseError: relation "roleAllocations" does not exist

I've tried to find any spelling errors, no luck. From what I've found online it's said to be a trigger in the database that might be changing the name of the table. There seems to be no triggers in the database.

PostgreSQL@9.5.13


texts: Clarification on Cardinality and Participation

texts:
I'm currently redesigning a schema with 20 or so tables and my first task is to create an ERD from the existing tables/constraints/relationships. This has proven more difficult than I expected because while I'm good at creating ERDs from business rules, extracting that same information from a database that was poorly designed has been challenging.

My specific questions revolve around translating the constraints and relationships from the DDLs into cardinality and participation on the ERD. An example:

The following DDLs for two sample tables (I included only relevant attributes, ie FKs and PKs):

CREATE TABLE SCHEMA.SCHEDULE (
    SCHEDULE_ID NUMBER(22,0),
    CONSTRAINT SCHEDULE_PK PRIMARY KEY (SCHEDULE_ID)
) ;

CREATE TABLE SCHEMA.OBJECTS (
    OBJECT_ID NUMBER,
    SCHEDULE_ID NUMBER,
    CONSTRAINT OBJECT_PK PRIMARY KEY (OBJECT_ID),
    CONSTRAINT OBJECTS_SCHEDULE_FK FOREIGN KEY (SCHEDULE_ID) REFERENCES SCHEMA.SCHEDULE(SCHEDULE_ID)
) ;

From the above DDL, I understand the following:
- SCHEMA.SCHEDULE is the parent, and SCHEMA.OBJECTS is the child

I then was able to make the following ERD:

What I can't figure out, is the cardinality and participation. I am guess that for SCHEDULE it is (1,M) and OBJECTS it is (0,m). Is this correct?

Any resources or excellent clear explanations with lots of examples is also appreciated. I am creating the ERD with min/max notation.

Thank you.


texts: PostgreSQL strategies to deal with disk filling up

texts:
I'm using PostgreSQL (8.4) to store data produced by an application making frequent inserts (in the table structure described below).

The database keeps growing with time and, since the newer data is more relevant than the older data (in this particular application), deleting the older rows is a reasonable solution (either based on lower id or older input_datetime, which is more or less the same).

To prevent issues related from this database (the only database running on this server) from affecting the rest of the system, I've put the PostgreSQL data directory on its own partition (ext3, on a Linux system). Nevertheless, when this partition becomes full, this causes a number of problems.

I'm thinking of deleting older data regularly (e.g. DELETE FROM data_group WHERE id <= ... via a cron job) to deal with this.

Firstly, my understanding of VACUUM (as performed by auto-vacuum, which is on) is that, while it doesn't necessarily give back the disk space to the OS (like VACUUM FULL would), it still allows some new data to be inserted within the disk space already used (that is, the DELETEs don't necessarily affect the file size, but they still free space in PostgreSQL's own data structures). Is this correct? (I've noticed VACUUM FULL caused a few problems with the application itself, probably because of the locks it uses.)

If so, it also appears that SELECT pg_database_size('my_database') reflects the size used on disk, which doesn't necessarily reflect what's available for further inserts. Is there another way to estimate how much space is available for new inserts?

In addition, when it's too late and the partition is filled at 100%, running this DELETE statement causes this error and crashes the PostgreSQL service:

  PANIC:  could not write to file "pg_xlog/xlogtemp.7810": No space left on device

The PostgreSQL daemon stopping is of course a major issue (and there is no other disk to move the cluster to on this machine).

Are there general strategies to prevent this sort of problem from occurring (knowing that disk space is constrained within a given partition, but that it can be acceptable to delete older data)? I would like to automate as much of this as possible, without root or postgres (or PostgreSQL admin) intervention.

CREATE TABLE data_group (
    id SERIAL PRIMARY KEY,
    name TEXT,
    input_datetime TIMESTAMPTZ
);

CREATE TABLE data_item (
    id SERIAL PRIMARY KEY,
    group_id INTEGER NOT NULL REFERENCES data_group(id) ON DELETE CASCADE ON UPDATE CASCADE,
    position INTEGER NOT NULL,
    data BYTEA
);


texts: How do you calculate the CPU Affinity Bitmap?

texts:
I am trying to calculate the bitmap for my 32-core server. I want my instance to use cores 30 and 31.

I know that CPU 0 gets assigned a value of 1, CPU 1 gets assigned a value of 2, CPU 3 gets assigned a value of 4, etc.

I think this means that CPU 30 would have a value of 1073741824 and CPU 31 would get a value of 2147483648, but 1073741824 + 2147483648 = 3221225472, which is higher than the max value of an int and not acceptable to sp_configure

After 16 CPUs, does it flip to start using negative numbers or something?

I can't use ALTER SERVER CONFIGURATION to do this, because I am on SQL Server 2008.

I can't use the GUI, because all my processes need to be reusable on Server Core instances.

I also want to understand the theory and what I am missing from it!!!


texts: Remove Local RAID Drive from MS SQL Windows Cluster

texts:
We recently stood up a production SQL Server 2014 Enterprise on a two-node Windows Failover cluster (Active/Passive).  We had some SSD's laying around and decided to use them for the tempdb for one of the two instances hosted on this cluster.  The thought was local storage plus SSD = win.

We did run Dsksp, SQLIO, etc to test this setup before going into production and everything checked out ok.  However, once we got went live, we started to see significant I/O wait issues during (and only during) our nightly DB integrity check.

After trying a few things in an attempt to resolve the issue, we decided to move the tempdb off to our SAN storage--which cleared up the I/O wait issues.

We now want to remove the SSD from each node.  Before we do so, I want to make sure I understand the process correctly.

These were local disks so they were not assigned as a cluster resource.

From what it appears, now that the tempDB has been moved, we simply need to remove the logical drive from the OS (i.e. take offline/delete), then delete the virtual disk from the RAID controller.

Is it this simple or do is there something more that needs to be in SQL or in the cluster configuration?

I have seen articles about removing shared storage, but nothing touching on local and since this is our first cluster, I want to make sure there isn't anything hidden that will cause down time or otherwise ruin our day.

Thanks


texts: Why would I need a third table in a one-to-many relationship?

texts:
Good day,

Our db professor at the univeristy always said that one-to-many relationships with (0, M) in one side MUST have a third table to relate them. I did not ask him back then and now I cannot, but I would like to know why would he claim that? (specially the must part).

I am modeling a simple sensor-measure-campaign database and I am getting really puzzled with it, what do you think about my model, would it work as I expect? This relates to my question because I am doing something completely different to what I was taught, and I am afraid of building a broken model.

Sensor has 0 or M measures, a measure belongs exactly to one sensor.
Campaign has 0 or M sensors, a sensor may be in 0 or N campaigns.
Campaign has 0 or M measures, a measure belongs exactly to one campaign.

Using my professsor's approach, I get 6 tables (an intermediate table for each pair). I see no need for 2 of those tables, but understanding that is the purpose of this question.

Ignoring him, I got Campaign and Sensor related by both Measure and another table (third table from many-to-many, which I called Instrument). I think it looks wrong to have such a double relationship, noticing that both Measure and Instrument will have FK to Campaign and Sensor (Instrument would be like an empty measure, IMO), I decided to simply do what I did.

I potentially need to query any combination (sensors/measures for a given campaign and measures for a given sensor), and I think I can easily (?) do that (using subqueries when measure is involved). I also want that if I delete a campaign or a sensor, its measures get removed, I can easily achieve that with an on delete cascade. What would be the drawbacks?

Sorry for the newbie question, any help will be appreciated. I already googled and found nothing, maybe I used the wrong terms, I am not pretending to spam here. At least a smarter query to Google Search will be appreciated, thanks.


texts: Sql subquery for nested parent child data

texts:
I have two tables , on parent table (categories ) and child table (features).. I have this query

    select c.* ,
            (select GROUP_CONCAT(f.featuresId , f.featuresTitle )
            from applika.features f
            where f.categoriesId = c.categoriesId
            group by categoriesId) as nestedData
        from applika.categories c

PROBLEM : but in nestedData column my subquery in group_concat show the result inside quotes like this :

 "nestedData": "featuresId:1featuresTitle:title1 ,featuresId:2featuresTitle:title2 ,featuresId:3featuresTitle:title3 ,featuresId:4featuresTitle:4"

REQUEST :  but I don't want to show data in quotes , is there another way instead of GROUP_CONCAT or CONCAT to select my child data in ordinary array of objects like this for example :

    [
        ...

 "nestedData": [
          {"featuresId":1 , "featuresTitle":"title1"},
          {"featuresId":2 , "featuresTitle":"title2"} ,
          {"featuresId":3 , "featuresTitle":"title3"},
          {"featuresId":4 , "featuresTitle":title4"}
        ]
    ]


texts: Sub-Query group-by count(*) not returning 0

texts:
Prefix: I am working on an application that is tracking employee training time and I need to get the total number of employees that have reached 75 total hours of training. After researching for some time I have determined I should probably use a join clause but joins are not my strength.

Question: For the following query I am having a problem getting the 'completed' subquery to return a 0 value.

SELECT
    Completed.Total_Complete "Completed Training",
    Incomplete.Total_Incomplete "Incompleted Training"
FROM

    (
     SELECT
        count(*) AS Total_Complete
     FROM
        EMPLOYEE_TRAINING_RECORD
     HAVING SUM(TRAINING_HOURS) >= 75
    )
    Completed,

    (
     SELECT
        count(*) AS Total_Incomplete
     FROM
        EMPLOYEE_TRAINING_RECORD
     HAVING SUM(TRAINING_HOURS) < 75
    )
    Incomplete
;

Thanks for any help.


texts: postges: psql csv import fails although file exists

texts:
Trying to import a csv file as follows:

psql -h 127.0.0.1 -d jira_statistics -U admin -p 5432 -c "COPY bugs FROM '/home/centos/bugs.csv' delimiter '|' csv;"
Password for user admin:
ERROR:  could not open file "/home/centos/bugs.csv" for reading: No such file or directory
HINT:  COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy.

However both the file and the table exist in jira_statistics db

[root@jira-statistics ~]# ls /home/centos/bugs.csv
/home/centos/bugs.csv

[root@jira-statistics ~]# psql -h 127.0.0.1 -p 5432 -U admin jira_statistics
Password for user admin:
psql (9.2.24, server 11.1 (Debian 11.1-3.pgdg90+1))
WARNING: psql version 9.2, server version 11.0.
         Some psql features might not work.
Type "help" for help.

jira_statistics=# \dt
         List of relations
 Schema |   Name   | Type  | Owner
--------+----------+-------+-------
 public | bugs     | table | admin
 public | devtasks | table | admin
 public | releases | table | admin
 public | stories  | table | admin


texts: Should I backup Master key / Service master key if I'm using TDE?

texts:
I've done this in the past, and now I need to do it again. activate TDE.
I remember that I didn't need to restore the master key on the secondary server to restore the database.
And now, as I have bad memory, I'm doing dozens of tests again with sintax, backup and restore and etc to be sure we will not mess with anything after enabling TDE in the database.
after creating the MASTER KEY, then CERTIFICATE, then the DATABASE ENCRYPTION KEY , I just backup the CERTIFICATE as below:
USE MASTER
GO
BACKUP CERTIFICATE CERTIFICATE_I_HATE_TDE
    TO FILE ='C:\tde_backups\CERTIFICATE_I_HATE_TDE.CER'
    WITH PRIVATE KEY ( FILE ='C:\tde_backups\CERTIFICATE_I_HATE_TDE_PRIVATE_KEY.PVK',
    ENCRYPTION BY PASSWORD='123superHardPassword')

then, I enable the encryption with ALTER DATABASE SET ENCRYPTION ON.
After this I backup the database, and to restore in the other server, I create a certificate FROM FILE pointing the old certificate (that was CTRL+C / CTR+V to the new server).
After this I'm able to restore the database, without restoring the MASTER KEY or the SERVICE MASTER KEY.
I'm asking this because I'm reading this redgate link and it says to backup the master key, but where would I use it?
and another question. whats the difference between creating the master key and certificate in the MASTER database, and creating the master key and certificate in the database itself ( the one i'm going to use TDE)?
Edit: Deleted by mistake.


texts:
I am new to SQL Server and am not sure about how the locking mechanism works here. Please help

Issue: Need to delete close to 5 mil records in a table. A website pulls data from that table quite frequently.

My understanding is: delete would lock only the row hence this shouldn't affect the website. Is that right? Then, what would happen if the website is trying to pull the record that is being deleted? I know I sound very amateurish but I badly need to understand these basics.

Could one of the gurus please share a link where I can read up about the various kinds of locks in SQL Server as well


texts: Microsoft SQL Server 2014 Nested From Query in Cross-Apply

texts:
When selecting from a nested query in an OUTER APPLY statement the nested query seems to be evaluated only once in certain circumstances.

Bug reported to Azure Feedback Forum: https://feedback.azure.com/forums/908035-sql-server/suggestions/39428632-microsoft-sql-server-2014-incorrect-result-when-s

Is this the expected behavior or am I missing something in the documentation or is this a bug in SQL Server?

Also, is there any possibility to force evaluation of the nested query for every row?

Test Case 1

  Evaluates nested FROM query for every row in VALUES (imho expected
  behaviour)

SELECT
    v,
    v2
FROM
    (VALUES (1), (2), (3), (4)) AS inner_query(v)
    OUTER APPLY (
        SELECT
            MAX(inner_v2) AS v2
        FROM (
            SELECT
                15 AS id,
                v AS inner_v2
        ) AS outer_query
        GROUP BY id
    ) AS outer_apply

Result:

| v | v2|
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |

Test Case 2

  It also evaluates nested FROM query for every row in VALUES (imho
  expected behaviour)

SELECT
    v,
    v2
FROM
    (VALUES (1), (2), (3), (4)) AS inner_query(v)
    OUTER APPLY (
        SELECT
            MAX(inner_v2) AS v2
        FROM (
            SELECT
                15 AS id,
                v AS inner_v2
            UNION ALL
            SELECT
                id AS id,
                TestCaseTemp2.v AS inner_v2
            FROM
                (VALUES (1337, 0)) AS TestCaseTemp2(id, v)
            WHERE TestCaseTemp2.v != 0
        ) AS outer_query
        GROUP BY id
    ) AS outer_apply;

Result:

| v | v2|
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |

Testcase 3

  Evaluates nested FROM query only once

CREATE TABLE TestCaseTemp
(
    id int,
    v int
);
INSERT INTO TestCaseTemp VALUES (1337, 0);

SELECT
    v,
    v2
FROM
    (VALUES (1), (2), (3), (4)) AS inner_query(v)
    OUTER APPLY (
        SELECT
            MAX(inner_v2) AS v2
        FROM (
            SELECT
                15 AS id,
                v AS inner_v2
            UNION ALL
            SELECT
                id AS id,
                TestCaseTemp.v AS inner_v2
            FROM
                TestCaseTemp
            WHERE TestCaseTemp.v != 0
        ) AS outer_query
        GROUP BY id
    ) AS outer_apply;

DROP TABLE TestCaseTemp;

Result:

| v | v2|
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |


texts: Should tempdb be larger on a secondary replica?

texts:
I am working on SQL Server 2012 with Availability Groups.

On the secondary replica, the size of the tempdb datafile is roughly double that of the primary.  I am struggling to find information on whether this is normal or not?

The secondary replica is set up as a read-only replica.  All the server agent jobs check if they are executing on the primary node when they run, apart from backup jobs which only run on the secondary.  As far as I am aware we don't have any client applications that access the secondary replica directly.

Size examples (from sp_helpfile):

Primary replica - tempdev: 12259456 KB
Secondary replica - tempdev: 24828992 KB


texts: postgresql being slow on count distinct for dates

texts:
I have a very simple, but very big, table.
Its schema is like this

(yadda int, yadda1 int, yaddate date, ... other stuff).

Now, yaddate has an index by itself and it is also in other indexes together with other columns (eg. (yadda1, date)).

The table itself is some 100M rows.

When I run

  select distinct date from mybigtable;

the time needed to get the list is in the range of 200 seconds.
Explain Analyze tells me it's doing a seq scan and I don't understand why, since I the index is there.

First thing I am trying is reindex on the date only column index.

Am I doing something wrong?
Since obviously there's something I am missing about seq and index scan, can someone shed some light?
How can I make that query faster?

TIA.


texts:
Setup:
Windows server 2016 standard edition.
SQL server 2016 standard edition.
2 nodes configured in a cluster without witness quorum.
all member of the same Active Directory domain.
1 Availability group for 1 Database only.
1 listener.
Creation went well, and any manual SQL or Cluster fail-over are working properly.

dns domain: mydom.local
Node1 machine Name: Mynode1
Node2 machine Name: Mynode2
Cluster name: mycluster
Listener name: mySQLlistener
Availability Group: MySQLAG

But since then I have regularly this error message in my Cluster logs:
Source: Microsoft-Windows-FailoverClustering

Event ID: 1257

Cluster network name resource failed registration of one or more associated DNS names(s) because the access to update the secure DNS Zone was denied.

Cluster Network name: 'MySQLAG_mySQLlistener'
DNS Zone: 'mydom.local'

Ensure that cluster name object (CNO) is granted permissions to the Secure DNS Zone.

I found this ressource and this ressource which propose to recreate the CNO DNS record, but in the error message it is not the CNO for which it raise an error it is a Network name I don't use at all…  Built with the Availability Group + ListenerName.

Which is even more strange is that this network name is created with an "_" which is not "legal" for host names as per my understanding.
so I'm wondering if I'm not having another issue…

Any idea why it raise this error would be much appreciated.


texts: Combine multiple queries into single query

texts:
I am trying to run the following query:

SELECT Freight.Freight_No,
       Freight.Available,
       Locomotive.Loco_No,
       Locomotive.Available,
       Driver.Driver_No,
       Driver.Name,
       Driver.Available
FROM Freight,Locomotive, Driver
WHERE Freight.Available = 'Y'
  AND Locomotive.Available = 'Y'
  AND Driver.Available = 'Y'
GROUP BY Freight.Freight_No, Locomotive.Loco_No, Driver.Driver_No;

What I am trying to do is check available freight wagons, locomotives and drivers. I can individually run a SELECT statements. However I want to combine into one query. I understand I can do this using UNION but I may not be going about it the correct way.

Tables

Freight

CREATE TABLE Freight
(
  Freight_No integer PRIMARY KEY, Serial_No integer UNIQUE,
  Freight_Wagon_Type_Name varchar(20)
    REFERENCES Freight_Wagon_Type(Freight_Wagon_Type_Name),
  Available varchar2(1)
     CONSTRAINT AvailableFreight CHECK(Available IN('Y','N')) NOT NULL
);

Locomotive

CREATE TABLE Locomotive
(
  Loco_No integer PRIMARY KEY,
  Serial_No integer UNIQUE,
  Loco_Class_No integer
    REFERENCES Locomotive_Class(Loco_Class_No),
  Loco_name varchar2(20),
  Available varchar2(1)
     CONSTRAINT Available CHECK(Available IN('Y','N')) NOT NULL
);

Driver

CREATE TABLE Driver
(
   Driver_No integer PRIMARY KEY,
   Name varchar2(20),
   Available varchar2(1)
     CONSTRAINT AvailableDriver CHECK(Available IN('Y','N')) NOT NULL
);


texts: "Table Data Import Wizard" option missing from drop-down menu

texts:
I have a schema with a few tables, each containing three or four columns.

I'd like to import some CSV data but can't seem to find the right option. I've right-clicked on the tables expecting there to be a "Table Data Import Wizard" and "Table Data Export Wizard" sandwiched between "Copy to Clipboard" and "Send to SQL Editor".

I can't seem to figure out why this is happening. Can somebody please offer some guidance?

Environment characteristics:

MySQL version: 5.5.57
OS: Ubuntu 14.04.1


texts: MySQL does not use index for wildcard-free query on an INT field - why?

texts:
So I have a table with two indexes, one on a VARCHAR field (url) and one on an INT field (id). If I do a query on either of those fields using a WHERE condition with the usual =, the index is used in both cases. But for LIKE queries, the following uses the index:

SELECT * FROM user WHERE url LIKE 'somevalue';

But this one does not:

SELECT * FROM user WHERE id LIKE 1;

Is there an explanation somewhere in the docs for this or does anyone have any ideas why this is so?


texts: MySQL: Configure the cascade limit (recursion depth)?

texts:
The MySQL docs say:

  A self-referential .. ON DELETE CASCADE [is allowed, but] ..
  Cascading operations may not be nested more than 15 levels deep.

In MySQL 5.7, is this number configurable?

I think max_sp_recursion_depth is only for stored procedures.


texts: PostgreSQL: Can the archive command for master and standby point to the same directory?

texts:
I use streaming replication and PITR (WAL files) in my cluster, currently I have different versions of postgresql.conf for the master and the standby server.

The only difference in the files is the archive command, that points to a different directory.

for example, on master I have:

archive_command = 'cp %p /dbckp/server1master/%f'

And on the standby:

archive_command = 'cp %p /dbckp/server2master/%f'

I figure that if the standby is not generating any archives while on 'standby mode' I could use the same directory and have a single postgresql.conf.


texts: Postgres Won't Start - incomplete startup packet

texts:
I have an issue that is preventing a Postgres server from starting.  The server fell over after it ran out of disk space, how ever this has now been resolved although now Postgres will not start.  It just keep writing the same lines to the log file as shown below.

It is not a cluster, it is a single Postgres server.   Does anyone have any ideas what to look at here?

Using Postgres 9.3

2015-02-23 17:49:35 UTC LOG:  redo starts at 75/202674D8
2015-02-23 17:49:35 UTC LOG:  incomplete startup packet
2015-02-23 17:49:36 UTC FATAL:  the database system is starting up
2015-02-23 17:49:36 UTC FATAL:  the database system is starting up
2015-02-23 17:49:41 UTC FATAL:  the database system is starting up
2015-02-23 17:49:41 UTC LOG:  incomplete startup packet

** UPDATE **

I ran strace on the running pid and I can see that it is getting a "Temporary resource in use" error when trying to access a file in the pg_clog dir.


texts: Is my.cnf for mysql 5.5 is suitable and well tuned for below hardware spec?

texts:
My Hardware Spec

Technology: Ivy Bridge
CPU: Intel Xeon E3 1245v2
Intel Smart Cache: 8MB
Cores / Threads: 4 / 8
Frequency: 3.4GHz+ / 3.8GHz Turbo Boost
RAM: 32 GB DDR3
Hard disk: 2x 2TB SATA3
Raid 1 by software
Bandwidth: 100 Mbps guaranteed

OS

ubuntu 12.04.2 LTS 64 bit server edition

Mysql

version 5.5.29 , 64 bit

My Application Architecture

https://stackoverflow.com/questions/11272776/using-phusion-passenger-nginx-running-same-rails-app-with-multiple-instance

So my server contains 100 rails apps with 100 mysql databases, each database having around 100 tables

My Traffic To The Server

10 to 200 (max) request per second

3000 to 10,000 (max) request per day

Connectivity Between Mysql and Rails

I use sockets  socket = /var/lib/mysql/data/mysql.sock

my.cnf generated by https://tools.percona.com/wizard for the above hardware spec

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/data/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default_storage_engine         = InnoDB
socket                         = /var/lib/mysql/data/mysql.sock
pid_file                       = /var/lib/mysql/data/mysql.pid

# MyISAM #
key_buffer_size                = 32M
myisam_recover                 = FORCE,BACKUP

# SAFETY #
max_allowed_packet             = 16M
max_connect_errors             = 1000000
skip_name_resolve
innodb                         = FORCE
innodb_strict_mode             = 1

# DATA STORAGE #
datadir                        = /var/lib/mysql/data/

# BINARY LOGGING #
log_bin                        = /var/lib/mysql/data/mysql-bin
expire_logs_days               = 14
sync_binlog                    = 1

# CACHES AND LIMITS #
tmp_table_size                 = 32M
max_heap_table_size            = 32M
query_cache_type               = 0
query_cache_size               = 0
max_connections                = 500
thread_cache_size              = 50
open_files_limit               = 65535
table_definition_cache         = 4096
table_open_cache               = 10240

# INNODB #
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 512M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 26G

# LOGGING #
log_error                      = /var/lib/mysql/data/mysql-error.log
log_queries_not_using_indexes  = 1
slow_query_log                 = 1
slow_query_log_file            = /var/lib/mysql/data/mysql-slow.log

I have seen that innodb_buffer_pool_size = 26G, so in 32 GB RAM at least 27 to 28 GB will used by mysql, so there is only less space for running 100 rails apps, so please somebody will edit and optimize my mysql config my.cnf posted in pastebin, to get enough space and speed for running both mysql and rails application.

I planned to use 100 databases connecting with 100 rails application in a single machine, so i use mysql sockets for connectivity, whether will have performance improvement compared to connecting using mysql with ports.

Update

More info about application

My application is not an mission critical application, it's just an student information system for schools. Mysql backup to remote ftp scheduled through webmin at 2 times per day , so i planned to put 100 schools in single server, so 100 rails apps + corresponding 100 mysql databases

Currently the application is not running , now i am at production set-up stage, so i need to have some suggestions  for my.cnf considering equal resource handling for rails and mysql.


texts: How to left join intermediate result table after filter ta(subquery)?

texts:
+---------+----------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                |
+---------+----------------------------------------------------------------------------------------------------------------------------------------+
| stuInfo | CREATE TABLE `stuInfo` (
  `stu_name` varchar(20) DEFAULT NULL,
  `stu_no` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------+

+----------+--------+
| stu_name | stu_no |
+----------+--------+
| Abcd     |      1 |
| Acmn     |      1 |
| Borne    |      1 |
| Bence    |      1 |
| Lucy     |      1 |
| Jone     |      2 |
| Lucy     |      2 |
| Lily     |      2 |
+----------+--------+

I want to get the number of (A*, 1), (B*, 1) as below

+----------+--------+
| A        | B      |
+----------+--------+
| 2        |      2 |
+----------+--------+

I consider of using left join.

select count(ta.stu_name) as A, count(tb.stu_name) as B
from (
    select stu_name, stu_no
    from stuInfo
    where stu_no=1
) filtered_table
left join filtered_table ta
    on stu_name like 'A%'
left join filtered_table tb
    on stu_name like 'B%';

However this doesn't work.
What I want to know is how can I first base on  some condition (my example, the condition is 'stu_no=1'), and then use the filtered table to do left join.
 I think the get the filtered table first can improve the performance as there are multiple left join tables(same table).

The worked sql is as below:

select count(ta.stu_name) as A, count(tb.stu_name) as B
from (
    select stu_name, stu_no
    from stuInfo
    where stu_no = 1
      and stu_name like 'A%'
) ta left join (
    select stu_name, stu_no
    from stuInfo
    where stu_no = 1
      and stu_name like 'B%'
) tb on 1=1;


texts:
I'm developing a solution related to classroom materials. The hierarchy for classes is as follows:

Course Type (e.g., Car Courses)

Course (e.g., Fixing Cars, Driving Cars)

Class (e.g., Fall semester class)

My problem deals with class materials. Currently I have a "Material" table that has MaterialId, Title, Href, etc. I need to associate Materials with 4 possible scenarios:

Global materials (material applies to all classes regardless of course or
course type)
Course Type materials
Course materials
Class specific materials

I'm currently handling global materials with a field in the Material table (IsGlobal), and I'm handling the other 3 scenarios using linked tables (MaterialCourseType, MaterialCourse, MaterialClass). The problem with this solution is it requires me to enforce data integrity through application code. For example, if I associate a material with a course type, I would need to delete all references to materials in the MaterialCourse and MaterialClass tables (since it's inclusion in MaterialCourseType would cover those). It is possible in my select statement to use a UNION which would automatically select distinct materials across all 4 tables, but that would leave invalid data. When the user through the front end decides to make a material available to the entire course instead of for a specific class, not cleaning up the MaterialClass table seems messy to me.

Is there a better database design I could use to enforce data integrity, or am I forced to deal with this problem in my application code to first delete invalid entries whenever a user associates a material differently?


texts: Ascending Key Problem - Leading column branded "Stationary" - SQL Server

texts:
I've been researching slow running queries in our database, and have concluded that this is a classic Ascending Key Problem.  As new rows are inserted almost constantly, and a given piece of SQL to pull the latest data out of the DB runs every 30 minutes, the first option of updating stats every 30 minutes sounded like it could be a waste of resource.

So, I looked into Trace Flag 2389 which in principle should help, however that requires the Leading column to be branded as Ascending, and when I used Trace Flag 2388 to check the (PK) index stats, I see that the leading column is actually branded as Stationary - as it is for several of the PK indexes on other tables updated at the same time.

There doesn't seem to be much guidance on what results in a branding of Stationary, however I did find KB2952101 that says if less than 90% of inserts were greater than the old maximum value, it would be classed as Stationary.  All our inserts are new submissions, and the leading column is a bigint IDENTITY column, so 100% of inserts should be greater than the maximum previous value.

So my question is why would the column be branded as Stationary, when it is obviously Ascending?

An earlier attempt to resolve this problem for some daily running SQL (which worked really well) resulted in a job being setup to update statistics for this table nightly.  The update doesn't do a FULLSCAN, so could it be that the sampled scan is missing the new rows sometimes, so it's not always showing as ascending?

The only other thing I can think of that might affect this, is that we have an archive job running behind the scenes deleting rows over a certain age. Could this have an affect on the branding?

The server is SQL Server 2012 SP1.

Update: Another day, another stats update - same stationary branding.
There have been 28049 new inserts since the previous stats update.  Each row has a timestamp of when it was inserted, so if I select max(id) from table where timestamp < '20161102' I get 23313455
Similarly, if I do that for when the stats were updated today, I get 23341504.

The difference between these is the 28049 new inserts, so as you can see, all the new inserts were given new ascending keys (as expected), suggesting the leading column should be branded as ascending rather than stationary.

During the same period, our archiving job deleted 213,629 rows (we're slowly clearing old data).  Is there any chance that a reducing rowcount could contribute to the stationary branding?  I've tested this before and it didn't look like it made any difference.

Update 2: Another day, another stats update, and the column is now flagged as Ascending!
As per the theory about deletes affecting this, I checked the percentage of updates being inserts compared to deletes, and yesterday 13% were inserts, whereas the previous two days inserts accounted for about 12%. I don't think that gives us anything conclusive.

Interestingly, a related table that gets on average 4 rows inserted for each row inserted into this main table, and has it's stats updated at the same time, has it's IDENTITY PK column still as Stationary!?

Update 3:
Over the weekend we get more inserts.  This morning the leading column is back to Stationary.  On the last stats update, we had 46840 inserts and only 34776 deletes.

Again, interestingly, the related table I mentioned above now has it's leading column branded as Ascending.  Is there no documentation that can explain this?

Update 4:
It's been a week or so now, the archiving job has cleared the backlog, so we're consistently deleting about two thirds of the number of rows being inserted.  The stats are showing mixed results across the related tables, with one showing stationary, and two showing ascending, despite them all being updated proportionally similarly.


texts: MAX Query returns set results when using dbmail

texts:
I am setting up alerts in a system and I want to query a db table and get the max date passed in to a data row and only that one entry.

For some reason, when I do it, the HTML email that comes through has the same date over and over again.

How do I get it to where it stops repeating itself and just does the max date only?

SET @tableHTML16 =
    N'
BDI Feeds Last Received Batch
' +
    N'
' +
    N'
Cerner	CPACS	DSCH123	' +
    CAST  ((SELECT   td = (select max(CPProcessedDateTime) from eiwdata..eiwtcontentreceived (nolock) where CapturePointDesc='OneC_Cerner_BDI:OCBDI1'), '',
                     td = (select max(CPProcessedDateTime) from eiwdata..eiwtcontentreceived (nolock) where CapturePointDesc='OneC_CPACS_BDI:OCBDI1'), '',
                     td = (select max(CPProcessedDateTime) from eiwdata..eiwtcontentreceived (nolock) where CapturePointDesc='OneC_DSCH123_BDI:OCBDI1'), ''

            from eiwdata..eiwtcontentreceived (nolock)

        FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'
' ;

You can see what it looks like when it gets to this portion of the script by looking in sysmail all items:

BDI Feeds Last Received Batch

Cerner	CPACS	DSCH123

2018-02-28T17:28:13.450	2018-02-28T16:31:51.113	2018-02-28T16:55:35.433

2018-02-28T17:28:13.450	2018-02-28T16:31:51.113	2018-02-28T16:55:35.433

2018-02-28T17:28:13.450	2018-02-28T16:31:51.113	2018-02-28T16:55:35.433

2018-02-28T17:28:13.450	2018-02-28

I of course had to shorten it, but it just goes over and over again.


texts: InnoDB engine lock tables higher than tables used

texts:
I am running the SQL statement
CREATE OR REPLACE TEMPORARY TABLE my_schema.tmpx_test AS
SELECT col1
     , SUBSTR(col2, 1, 5) AS col3
FROM my_schema.my_table
WHERE col4 = 'my_string'
AND POSITION('val:' IN col5) != 0
AND date_col > '2020-10-11'

While the query is running, I am executing
SHOW ENGINE INNODB STATUS

and the transaction details for the query are

mysql tables in use 2, locked 3
397195 lock struct(s), heap size 41721976, 18721097 row lock(s), undo log entries 396
MySQL thread id 17, OS thread handle 139896414131968, query id 413 my_ip my_user Sending data

Am I right in assuming that the two tables in use in the tempx_test and my_table? Also, how come the number of tables locked is higher than the number of tables in use?
Isolation level is repeatable-read.


texts: If I kill a looping INSERT query, will it rollback all inserts since the loop started or only the last iteration?

texts:
Do all inserts or only the current one rollback if the following loop query was killed in the middle of looping?

DECLARE @LoopId INT = 1;

WHILE (@LoopId < 100)
BEGIN
    INSERT INTO Table2
    SELECT Field1
    FROM Table1
    WHERE ForeignKeyField = @LoopId

    SET @LoopId = @LoopId + 1;
END


texts: I do not perform SUM with conditions in SQL Server

texts:
I want to make SUM of a colummn according to various conditions. In this example for the column 'fac_saldo_tot' if value is greater than 500 add the column and group them down the column 'cli_razon_soc'

I have made this query but I do not perform the operation it only stores the higher values to 500 and otherwise it puts 0.
 see image (red column)

    SELECT        cli_razon_soc,
   CASE WHEN t .fac_saldo_tot > 500 THEN SUM(t .fac_saldo_tot) ELSE 0 END AS 'Mayor500'
FROM            cdc_vw_BuroCredito_Facturas95 AS t
GROUP BY cli_razon_soc, fac_saldo_tot
ORDER BY cli_razon_soc, 'Mayor500'

I appreciate the suggestions you can give me

This is the result I intend


texts: multiple connections on MySQL database with no users on httpd


texts:
I use SQL Server 2008 R2 and want to set value for fill factor property in each index. I look for recipe to calculate best value for this property (have better performance when insert record) . Also how I can meter query performance by set deference value to this property.

Thanks a lot in advance.


texts: Getting error "must declare the scalar variable" in SQL function even though it is declared

texts:
I am altering function like:

ALTER FUNCTION [dbo].[fn_CalculateListing](@Listing TypeListingDatePrice READONLY)
RETURNS TypePreviousListingResult
AS
BEGIN
    DECLARE @tbl_ListingDateDetails TypePreviousListingResult
    RETURN @tbl_ListingDateDetails
END
GO

But throwing error as:

Msg 137, Level 16, State 1, Procedure fn_CalculateListing, Line 6
Must declare the scalar variable "@tbl_ListingDateDetails".

Why this giving error and asking for declaration even though I have declared that before using that variable?

TypePreviousListingResult is a table type created as CREATE TYPE TypePreviousListingResult AS TABLE....

Previous question: How to write function in sql which accept table as input and return result back as table?


texts: MySQL : Does 'bytes_sent' and 'bytes_received' include mysqldump data?

texts:
I'm trying to estimate my db traffic not including backups (mysql 5.1.41).

I use show global status; to get the parameters bytes_sent and bytes_received.

Questions

Do these numbers include mysqldump (backup) data or not ?
Do they include the log file which is being read by slaves or not?

Thanks,
Niro


texts:
I am practising my MySQL in Leetcode and I've encountered this question:

  The Trips table holds all taxi trips. Each trip has a unique Id, while Client_Id and Driver_Id are both foreign keys to the Users_Id at the Users table. Status is an ENUM type of (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’).

+----+-----------+-----------+---------+--------------------+----------+
| Id | Client_Id | Driver_Id | City_Id |        Status      |Request_at|
+----+-----------+-----------+---------+--------------------+----------+
| 1  |     1     |    10     |    1    |     completed      |2013-10-01|
| 2  |     2     |    11     |    1    | cancelled_by_driver|2013-10-01|
| 3  |     3     |    12     |    6    |     completed      |2013-10-01|
| 4  |     4     |    13     |    6    | cancelled_by_client|2013-10-01|
| 5  |     1     |    10     |    1    |     completed      |2013-10-02|
| 6  |     2     |    11     |    6    |     completed      |2013-10-02|
| 7  |     3     |    12     |    6    |     completed      |2013-10-02|
| 8  |     2     |    12     |    12   |     completed      |2013-10-03|
| 9  |     3     |    10     |    12   |     completed      |2013-10-03|
| 10 |     4     |    13     |    12   | cancelled_by_driver|2013-10-03|
+----+-----------+-----------+---------+--------------------+----------+

  The Users table holds all users. Each user has an unique Users_Id, and Role is an ENUM type of (‘client’, ‘driver’, ‘partner’).

+----------+--------+--------+
| Users_Id | Banned |  Role  |
+----------+--------+--------+
|    1     |   No   | client |
|    2     |   Yes  | client |
|    3     |   No   | client |
|    4     |   No   | client |
|    10    |   No   | driver |
|    11    |   No   | driver |
|    12    |   No   | driver |
|    13    |   No   | driver |
+----------+--------+--------+

  Write a SQL query to find the cancellation rate of requests made by unbanned clients between Oct 1, 2013 and Oct 3, 2013. For the above tables, your SQL query should return the following rows with the cancellation rate being rounded to two decimal places.

+------------+-------------------+
|     Day    | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 |       0.33        |
| 2013-10-02 |       0.00        |
| 2013-10-03 |       0.50        |
+------------+-------------------+

I have found a solution in the question's Discuss section, here it is:

select t.Request_at as Day, round(count(t.Status = 'completed' or null)/count(t.Status), 2) AS `Cancellation Rate`
from
Trips as t,
Users as u
where u.Users_Id = t.Client_Id
and u.Banned = 'No'
and t.Request_at between '2013-10-01' and '2013-10-03'
group by t.Request_at;

That is fine. But I want to change it to this:

select t.Request_at as Day, round(count(t.Status = 'completed')/count(t.Status), 2) AS `Cancellation Rate`
from
Trips as t,
Users as u
where u.Users_Id = t.Client_Id
and u.Banned = 'No'
and t.Request_at between '2013-10-01' and '2013-10-03'
group by t.Request_at;

and I get the wrong answer.

I don't understand what the or null is used for. Is it necessary? If so, why?


texts: How to select datas for a particular user between a date range

texts:
I am querying a table with where condition in which i want to choose between a particular date range at present my query is like this with a where condition.

WHERE entered_by = '1' AND date_created = '2018-08-23'

I want to use a particular date range from to to how can i do this?


texts: Where to learn SQL Server Service Broker?

texts:
Are there any good resources to learn Service Broker?
I'm looking for a newbie-friendly guide.


texts: Update column with another table's value

texts:
I have two tables, users (with columns email, name, id) and user_details (with columns username, address, details). Currently the username in user_details is populated with a mix of ID's and Email's. I want the table to have all ID's. Is there a way I can match the rows in user details that are currently populated with email to the row with the same email in the users table, and update it with the id of that row? I have tried the below query:

update
(select distinct
    u.sid as new_id,
    ud.username as id
from user_details ud
    inner join users u on
        lower(ud.username) = u.email) up set up.id = up.new_id;

to try and join the two tables and then update the join, but I am getting the following error:

  SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
  01779. 00000 -  "cannot modify a column which maps to a non key-preserved table"


texts: Oracle: Extract Graphical Database Representation

texts:
Possible Duplicate:
  Oracle Creating ER Diagrams and Data Dictionaries

How can I extract a graphical (schema) representation of an Oracle database (tables and their relationships; with colums and datatypes....). Are there (free) tools, which can do this?

I think the Oracle SQL Developer has no such feature...


texts: Unable to find how a table is being populated

texts:
Using SSMS2014 w/ SQL Server 2008 R2.
I have a table that I am trying to find out how and from where data inside it is coming from. (I didn't create the table and am unable to contact the person who did)
I've tried the following:

RMB on the table -> View Dependencies.
Result: This comes back empty.
I've tried running the following code:

select object_name(object_id) as DatabaseName, last_user_update, *
from sys.dm_db_index_usage_stats
where database_id = DB_ID('PackardIntranet')
and object_id=object_id('tbl_Johnstone_Packard_SalesReport')

Result: This just shows NULLs and 0's except when I queried the table previously
I've tried using RedGate's SQL Search using the table name or a column name as a parameter.
Result: The only result is the table itself

Am I missing something or can it be that this table was just created and populated once by the person who created it? Or can this be part of a script that is used to create and populate this table? Is there any way I can attempt to find out more about this table?


texts: Why does sp_rename not affect the output of sp_helptext?

texts:
create procedure Eg_Proce
as
begin
select * from po;
end

go

exec sp_rename 'Eg_Proce','Example_Procedure'

After renaming procedure

sp_helptext 'Example_Procedure'

The definition of the procedure is not updated

create procedure Eg_Proce
as
begin
select * from po;
end


texts:
I know that MySQL is tested more on Linux (Oracle has it's own linux distribution and you canot have LAMP wit MySQL (meaning MySQL has always focused more on Linux)

My intuition says that MySQL has a better performance on Linux than on any other OS (including FreeBSD based MacOS). But I don't have facts to back it up.

Can you point me to any references  which can help me determine whether moving MySQL from MacOS to Linux would give a performance boost= What has been your own experience?

I guess the only other way is to measure difference KPIs on both Linux and MacOS and then try to compare them. But I am trying to put more justifications for purchase of Linux ready hardware and cannot compare the two at the moment.


texts: Relate few records in table to eachother

texts:
I need to relate groups of records, like I have 100 records in table, 3 of them should be known they are related, and other 5 are related to own group.
I don't need any group IDs or anything like that, I just need easy way to all records bound to given one.
Additionally, only very small part of records will need this, so I tried avoiding additional fields in table, but failed so far.
My best guess would be storing group id in the main table and somehow keeping track of highest group id used to not break anything (getting max value would work probably?)
It seems messy and somehow like too much overhead to me, I'd appreciate any suggestions

EDIT: my main table contains autoincremented unique id for each record, and rest of table might be considered blob, it is using innodb
for now I need to bind records created as batch, but I'm afraid it might be needed to bind already existing records (and I don't really want to mess too much with code maintaining the database, so I though of first creating all records, gathering their ids and then binding them)

EDIT2: I'm using ORM to manage DB records (Eloquent to be precise), so my question was purely about DB structure and I may even decide to use secondary table to store bulk ids and then create new bulk entity and then create new records with bulk id set for them


texts: Is it possible to remote connect to SQL Server 2008 database with SQL Server Management Studio 2005?

texts:
I have SQL Server Management Studio 2005 R2 in my administrator PC, and SQL Server Management Studio 2008 in the Server PC. Each time I tried to connect to the database server (SSMS 2008), it will throw errors that indicated I have different version of database or the software or else (db instance some said?).

I had search the internet. The most correct solution was to use Cumulative update package 5 for SQL Server 2005 Service Pack 2 to connect to 2008.

But my questions is, is there any other solution that doesn't need me to update or install any patch for the SSMS 2005 ? Maybe like tweaks or any other fast solution ? Any comments appreciated! Thanks!


texts: Copying a database that is in Retore status

texts:
I have a database that needs to stay in restore mode (NonRecovery), I receive a new group of log files that I receive daily that I have to post to it.
How can I copy the database while it is in non recovery mode so I can use it, but leave the original in restore mode.


texts: In MySQL, Is there a way to display all tables contained in a view

texts:
I have many views in my database and I wanted to do some statistics about table usage (not dynamic statistics but static ones).

Like for this view:

CREATE VIEW V_LOWER_COST AS
    SELECT SUPPLIER_NUMBER, A.ITEM_NUMBER,UNIT_COST, SUPPLIER_COST
        FROM INVENTORY_LIST A, SUPPLIERS B
        WHERE A.ITEM_NUMBER = B.ITEM_NUMBER
        AND UNIT_COST > SUPPLIER_COST

... I would get something like this:

view-name       | tables
V_LOWER_COST    | INVENTORY_LIST, SUPPLIERS

and for the view:

CREATE VIEW SALESORDERTOP AS
    SELECT X.SALESORDERNUMBER, X.TOTALDUE, X.COMPANYNAME, C.UNIT_COST
    FROM V_LOWER_COST C
       INNER JOIN TABLEX X ON C.KEY = X.KEY
       LEFT JOIN TABLEY ON X.KEY = Y.KEY
    WHERE TOTALDUE > 10000.0
    AND Y.REGION = 'AU'

... I would get something like this:

view-name      | tables
SALESORDERTOP  | INVENTORY_LIST, SUPPLIERS, TABLEX, TABLEY

Note that the second view refers to the first one and the report will mention all implied table including the ones from the first view.

I tried many queries using the information_schema but was blocked at some point as not able to make a like which refers to other records.

Is there any existing command to find that easily otherwise what would be the best solution to do so?


texts: How to update table1 field by using other table and function

texts:
I have two tables and one function:

Table1 contains shop_code,batch_id

shop_code| batch_id  | regstriy_id
123 | 100  | 12
124 | 100  |13
125 | 100  |12

Table2 contains shop_code,shop_name

shop_code| shop_name
123 | need to populate
124 | need to populate
125 | need to populate

Function1 take parameter registry_id from table1 and returns shop_name

Table2 shop_name is empty I want to populate against the shop_code.

It will be great if someone can help, I am using Oracle.

I tried this but not working

 update TABLE2 set T2.SHOP_NAME = T.SHOP_NAME
 from(
 select GET_shop_name(t1.regitry_id) as shop_name ,
           t1.shop_code shop_code
           from TABLE1 T1
                ) t where t.shop_code = t1.shop_code;


texts: Using an index for both uniqueness and fast lookup?

texts:
Right now I have a postgresql 8.4 database set up for players in a multiplayer game. I want the username column to be unique. I also want to be able to lookup a player by username quickly. Here's the output of \d players:

                            Table "public.players"
   Column   |  Type   |                      Modifiers
------------+---------+------------------------------------------------------
 id         | bigint  | not null default nextval('players_id_seq'::regclass)
 username   | text    | not null
 trophies   | integer |
Indexes:
    "pk_id" PRIMARY KEY, btree (id)
    "players_username_key" UNIQUE, btree (username)
    "players_trophies_index" btree (trophies DESC NULLS LAST)
    "players_username_index" btree (username)

I'm not a DBA, so bear with me. This seems like an inefficient use of disk space by having two indices on the username column: one for uniqueness and one for fast lookup. Is it possible to combine them into one index, maintaining uniqueness and fast lookup? If so, are there any drawbacks to such an approach?


texts: Query challenge: Creating even sized buckets, based on a measure not row count

texts:
I'll describe the problem in terms of loading a fixed number of trucks with orders, as evenly as possible.

Inputs:

@TruckCount - the number of empty trucks to fill

A set:

OrderId,
OrderDetailId,
OrderDetailSize,
TruckId (initially null)

Orders are composed of one or more OrderDetails.

The challenge here is to assign a TruckId to each record.

A single order cannot be split across trucks.

Trucks should be as evenly* loaded as possible, measured by sum(OrderDetailSize).

* Evenly: The smallest achievable delta between the least loaded truck and most loaded truck. By this definition, 1,2,3 is more evenly distributed than 1,1,4. If it helps, pretend you are stats algorithm, creating even height histograms.

There is no consideration for maximum truck load. These are magic elastic trucks. The number of trucks however is fixed.

There’s an obviously solution which is iterative - round robin allocate orders.

But can it be done as set based logic?

My main interest is for SQL Server 2014 or later. But set based solutions for other platforms could also be interesting.

This feels like Itzik Ben-Gan territory :)

My real world application is distributing a processing workload into a number of buckets to match the number of logical CPUs. Hence each bucket has no maximum size. Stats updates, specifically. I just thought it was more fun to abstract the problem into trucks as a way of framing the challenge.

CREATE TABLE #OrderDetail (
OrderId int NOT NULL,
OrderDetailId int NOT NULL PRIMARY KEY,
OrderDetailSize tinyint NOT NULL,
TruckId tinyint NULL)

-- Sample Data

INSERT #OrderDetail (OrderId, OrderDetailId, OrderDetailSize)
VALUES
(1  ,100    ,75 ),
(2  ,101    ,5  ),
(2  ,102    ,5  ),
(2  ,103    ,5  ),
(2  ,104    ,5  ),
(2  ,105    ,5  ),
(3  ,106    ,100),
(4  ,107    ,1  ),
(5  ,108    ,11 ),
(6  ,109    ,21 ),
(7  ,110    ,49 ),
(8  ,111    ,25 ),
(8  ,112    ,25 ),
(9  ,113    ,40 ),
(10 ,114    ,49 ),
(11 ,115    ,10 ),
(11 ,116    ,10 ),
(12 ,117    ,15 ),
(13 ,118    ,18 ),
(14 ,119    ,26 )

--> YOUR SOLUTION HERE

-- After assigning Trucks, Measure delta between most and least loaded trucks.
-- Zero is perfect score, however the challenge is a set based solution that will scale, and produce good results, rather
-- than iterative solution that will produce perfect results by exploring every possibility.

SELECT max(TruckOrderDetailSize) - MIN(TruckOrderDetailSize) AS TruckMinMaxDelta
FROM
(SELECT SUM(OrderDetailSize) AS TruckOrderDetailSize FROM #OrderDetail GROUP BY TruckId) AS Truck

DROP TABLE #OrderDetail


texts: How is it possible to model in a database many different products?

texts: For example, a smartphone might have attributes such as RAM and camera resolution, whereas a fridge has way different attributes. Is there a way to store these data in a relational database?


texts:
I created a user tom(SQL Login) in SQL Server 2019 Standard edition. Then I remove and reinstall the SQL Server 2019 standard edition(same version) and restored all the databases again with the same setting which I did previously with same instance name. Everything is same. Even same machine is used. I copied all the logins through a script provided by Microsoft. I ran the script on new installation/instance and all the logins are back again with same privileges as was granted earlier.
End user tom is unable to view the new database that I created in the List of databases in the SSMS. Tom can view the previously created databases in the list but not the new one. I granted tom sysadmin rights but still he is unable to see the new db I created from his machine. I asked tom to download and reinstall new version of SSMS, which he did but still he is unable to view the new db I created. Interestingly, when i used his credentials to login from my own personal machine to check, that new database will be coming in his(tom) list from my own personal machine, but when he login from his own machine with the same credentials, that new db is not coming into his list of DB. If right/permission is an issue then why it is coming in my own personal machine when I used his credentials to check. So where is an issue? Is it firewall issue in tom machine? Or something else?


texts: SQL Server 2008 R2 - Moving nodes in mirrored environment

texts:
Was hoping that I would find this documented somewhere, but I've searched and searched. Finally asking the question myself:

I have SQL Server 2008 R2 in a HA setup (primary/mirror/witness)

I want to move the mirror and witness servers to different hardware from what they're currently on.

It's only 1 db and about 20GB in size, it uses SQL authentication for the db (not Windows auth) and the Windows SQL service will run with the same domain user on all nodes.

I assume I start by installing SQL Server on the new nodes, and adding the endpoints to each instance.

From there would I just remove mirroring from the primary instance and re-create mirroring, adding the new nodes? Would I need to restore from a backup on the new mirror instance?

Hopefully someone can lay out the steps involved, thank you.


texts: Difference between database vs user vs schema

texts:
I'm really confused with the terms database, user and schema. Can anyone explain how they are different from each other (if they are)?

If they are same then, what are the similarities between them? How do we use them? And how do we create them?


texts: In which server does the snapshot agent run?

texts: While configuring a publisher for replication, we execute sp_startpublication_snapshot SP and provide the publication name as a parameter to the SP. We do not specify the distribution server's login/password. So, I am confused if the snapshot agent runs on the publisher or distributor. Even if it runs on distributor then how can we trigger it from publisher (sp_startpublication_snapshot has to run at publisher as per MSDN tutorials)


texts: How do I get HeidiSQL to connect to Postgres databases for the first time?

texts:
With two different Linux servers I have installed Postgres on them using no standard configurations.  They are in different networks off-site.

The Postgres service is running (as I checked by running ps -ef | grep postgres).  I have installed namp on both Linux servers.

These commands show port 5432 is not blocked:

nmap -p 5432 x.x.x.x

I can SSH to either Linux server from my Windows machine.  I have Heidi SQL on my Windows desktop.  When I try to connect to either of these servers with HeidiSQL (using port 5432), I get "could not connect to server: Connection refused...is the server running on host "x.x.x.x" and accepting TCP/IP connections on port 5432."  This has never worked before.

What should I do to get HeidiSQL to connect to a Postgres database on a Linux server?


texts: Mismatched Oracle drivers - need upgrade (10.2.0.1 to 10.2.0.5)

texts:
I was forwarded here by recommendation from Stack Overflow ......

We are running Oracle Enterprise software on various client and DB servers and we are experiencing a problem (outlined here) where the apparent mismatch between the oracle drivers are causing the errors.

Our db servers are using 10.2.0.4 and 10.2.0.5 drivers and the clients are using 10.2.0.1.

Firstly, are the drivers the same on the Express and Enterprise editions so that if I update a development server using Express version (I dont have the authority to upgrade the Enterprise version nor the time to commission that) and it is successful, can I reasonably expect an upgrade to the Enterprise client versions on other servers to be safe/error free, baring environmental factors of course ?

Secondly, can I upgrade to version 10.2.0.5 now or do I have to go to the 11g version only as a full upgrade obviously carries its own share of issues ?

Can I simply upgrade the driver from 10.2.0.1 to 10.2.0.5 on the client sides or is it advisable to upgrade the software too ?

I hope I have explained this correctly and that it is understandable as I have asked this on several Oracle forums with very little response.


texts: What are the 'free to use' workload generators available for Oracle?

texts:
A couple that I know about are

http://www.dominicgiles.com/swingbench.html and
http://hammerora.sourceforge.net/

Are there any others? I am looking for tools what can generate random workload (based on some parameters) that can be saved (possibly as an SQL file) and run against an Oracle 11gR2 db separately.


texts: Database modelling : Creating a model for restaurant application which gives statistics

texts:
I am working on a server-side application in which users will be able to give ratings and comments to different factors of a 'Restaurant'. What the webapp does is takes all the ratings and creates statistics out of it. The statistics are based upon day, week, month and year. As well as other things like gender ratio, age ratio, etc.

I am free to design an optimal DB model for this, and I have done some of it, but would like to ask if this is optimal, and if not, how can optimize it so its easier to maintain, understand, error-free.

The model I have right now for statistics seems a bit complicated to calculate, maintain. I am looking for some easier options.

Here is the information the visitor will give us :

CREATE TABLE rating
(
  rating_id integer NOT NULL, // primary key
  age_range integer DEFAULT 0,
  comment_text character varying,
  friendly_stars numeric DEFAULT 0,
  gender character varying,
  other_stars numeric DEFAULT 0,
  overall_feedback boolean DEFAULT false,
  relative boolean DEFAULT false,
  quality_stars numeric DEFAULT 0,
  save_time timestamp without time zone
)

Here is the Statistics and monthly statistics I have. Monthly statistics will actually save daily computed statistics with month code, so it's easier to isolate months also from that was something I thought :

CREATE TABLE monthly_statistic
(
  mstat_id integer NOT NULL, // Primary key
  daily_value timestamp without time zone,
  female_ratio character varying,
  friendly_quality numeric DEFAULT 0,
  high_age_ratio character varying,
  less_age_ratio character varying,
  man_friendly numeric DEFAULT 0,
  man_ratio character varying,
  mid_age_ratio character varying,
  other_quality numeric DEFAULT 0,
  service_quality numeric DEFAULT 0,
  woman_friendly numeric DEFAULT 0,
  statistics_id integer NOT NULL, // assocication with Statistics
)

And finally, here is Statistics :

CREATE TABLE statistics
(
  statistics_id integer NOT NULL,
  less_age_month character varying, // Less age means 18-24
  less_age_week character varying,
  less_age_year character varying,
  mid_age_month character varying, // Mid_age means 24-60
  mid_age_year character varying,
  mid_age_week character varying,

 more_age_month character varying, // More age means 60+
  more_age_week character varying,
  moreageyear character varying,
  month_friendly numeric DEFAULT 0,
  month_man character varying,
  month_other numeric DEFAULT 0,
  month_quality numeric DEFAULT 0,
  month_woman character varying,

  week_friendly numeric DEFAULT 0,
  week_man character varying,
  week_other numeric DEFAULT 0,
  week_quality numeric DEFAULT 0,
  week_woman character varying,
  year_friendly numeric DEFAULT 0,
  year_man character varying,
  year_other numeric DEFAULT 0,
  year_quality numeric DEFAULT 0,
  year_woman character varying,
  restaurant_branch_id integer NOT NULL, // Association with resto branch
)

So as you can see I have created values for week, month and year. But if the user wishes to see last week or this weeks statistics, then I need something more fine-grained, that's why I created monthly-statistics, with which I can save information based on per day.

I have to also show information like, within 1 week or 1 month or 1 year, how many of them were males or females, how many were with age 18-24, etc. I hope you guys get the idea. Kindly let me know if this data-model sounds suitable or if you guys have any idea for a better one.

Please note, that statistics are calculated only in night, not real-time and I am using PostgreSQL.. Have a good day.


texts:
Here is an interview question I just had. I would like to know what a good answer to this would have been and what the different options would be.

  We have a stored procedure that is taking 26 seconds to return
  information that is grabbed from 5 tables. Our server times out after
  25 seconds. How can you solve this problem?

This is not verbatim but it is close to the question that was asked. I had no clue, and I don't even think we went into that kind of detail in our db class. All I could think of telling them is to make sure that the tables are indexed and I don't even know if that is right.

Here's an example of how this could look on creation:

CREATE PROCEDURE GetOrderForCustomers(@CustID varchar(20))
AS
BEGIN
SELECT * FROM orders o
    JOIN state s ON s.stateID = o.stateID
    JOIN customers c ON c.customerID = o.customerID
    JOIN items i ON i.itemID = o.itemID
    JOIN parts p ON p.partListID = o.partListID
WHERE c.customerID = @CustID
END

Once again this would be taking slightly more time than the timeout time allows.

What are my options for speeding up this stored procedure syntactically?
What are the different types of metadata that might help solve the problem?
What practices should be avoided that are seen in the procedure above?
(As a non-required aside question) Would it be acceptable to tell an interviewer that I would ask the real-life question on Stack Overflow?


texts: My question is using a guid(data type unique identifier) versus using identity specification(data type int). Are there any articles where it talks about why you would use one versus another? I'm at a new company using guid and it seems alot slower but maybe its my imagination. Amazingly this is the first place I've seen it used for the uniqueness of a row. Any help or thoughts you have as to why use one versus the other?


texts: Mysql Cluster 8 - "Jumpy" Auto Increment Values

texts:
Im using mysql cluster 8 - using PHP  to insert data I keep running into a problem where the Ids jump and then "recover" I.E

SELECT `MSU_ID`, `MSU_Date_Created` FROM `Material_Spreadsheet_Upload` ORDER BY `MSU_ID` DESC

603     2020-04-28 10:55:13
602     2020-04-28 10:55:07
601     2020-04-28 10:55:05
600     2020-04-28 10:55:03
599     2020-04-28 10:55:00
598     2020-04-28 10:54:57

SELECT `MSU_ID`, `MSU_Date_Created` FROM `Material_Spreadsheet_Upload` ORDER BY `MSU_Date_Created` DESC

100 2020-04-28 12:05:28
99  2020-04-28 11:54:49
98  2020-04-28 11:54:47
97  2020-04-28 11:54:45
96  2020-04-28 11:54:39

This is obviously a problem, Im not doing anything in-between inserting the data (I.E messing with auto_increment values)

Sinse the upgrade to mysql 8 my tables all have comments NDB_TABLE=READ_BACKUP=1 they enabled this by default according to the docs in Mysql 8 but im wondering if this is whats causing the issue

After the upgrade I noticed but then I had to write an run a bunch of SQL queries to fix the issue initially - but it keeps happening and im not sure how to prevent it


texts: How can I clear all configurations listed with the command show all on RMAN to default? I just found how to clear one unique configuration for exemple: CONFIGURE CHANNEL DEVICE TYPE DISK clear


texts: the REFERENCES privilege is only about creating a foreign key constraint? Practical use cases?

texts:
Today I learned about GRANT REFERENCES. In years of SQL admin and dev work I never heard about it and never had issues with it.

quote from MySQL 5.7 Reference Manual  / GRANT Syntax

  REFERENCES    Enable foreign key creation. Levels: Global, database,
  table, column.

quote from PostgreSQL 9.6 / GRANT

  REFERENCES To create a foreign key constraint, it is necessary to have
  this privilege on both the referencing and referenced columns. The
  privilege may be granted for all columns of a table, or just specific
  columns.

is GRANT REFERENCES only about creating a foreign key constraint? In what business case does it make sense to forbid creating a foreign key constraint (but allow to create tables)? Can you give me real world examples?


texts:
I'm having a tough time optimizing a read query from PostgreSQL 10. The query is as follows

SELECT mp.id_person, (
    COUNT(CASE
          WHEN mp.id_g_type = mc.id_g_type
               (and mc.g1 = mp.g1 or mc.g1 = mp.g2 or mc.g2 = mp.g1 or mc.g2 = mp.g2)
          THEN 1
          END
         ) / CAST(COUNT(mp.id_g_type) AS decimal)) as probability
FROM significant_mg mp
INNER JOIN significant_mg mc on mp.id_g_type= mc.id_g_type
WHERE mp.id_person!= 28076 AND mc.id_person= 28076
GROUP BY mp.id_person
HAVING COUNT(CASE
          WHEN mp.id_g_type = mc.id_g_type
               and (mc.g1 = mp.g1 or mc.g1 = mp.g2 or mc.g2 = mp.g1 or mc.g2 = mp.g2)
          THEN 1
          END
         ) / CAST(COUNT(mp.id_g_type) AS decimal) > 0.97

I've created an indices, one on the id_person column, the other one on id_g_type column. The significant_mg is not a table but a materialized view that contains columns id_person, id_g_type (both bigints) and columns g1 and g2 (both smallints).

The id 28076 in the where clause won't be fixed as I only use this id for testing purposes, it will be replaced by a variable when I'm done optimizing the select.

The result of explain analyze is as follows

"Finalize HashAggregate  (cost=140784.56..141226.84 rows=6552 width=40) (actual time=2869.754..2891.226 rows=14 loops=1)"
"  Group Key: mp.id_person"
"  Filter: (((count(CASE WHEN ((mp.id_g_type = mc.id_g_type) AND ((mc.g1 = mp.g1) OR (mc.g1 = mp.g2) OR (mc.g2 = mp.g1) OR (mc.g2 = mp.g1))) THEN 1 ELSE NULL::integer END))::numeric / (count(mp.id_g_type))::numeric) > 0.97)"
"  Rows Removed by Filter: 19818"
"  ->  Gather  (cost=136165.16..140293.13 rows=39314 width=24) (actual time=2796.706..2823.029 rows=59496 loops=1)"
"        Workers Planned: 2"
"        Workers Launched: 2"
"        ->  Partial HashAggregate  (cost=135165.16..135361.73 rows=19657 width=24) (actual time=2744.110..2753.896 rows=19832 loops=3)"
"              Group Key: mp.id_person"
"              ->  Nested Loop  (cost=0.43..103303.58 rows=1593079 width=32) (actual time=11.259..1589.526 rows=1283988 loops=3)"
"                    ->  Parallel Seq Scan on significant_mg mc  (cost=0.00..44870.04 rows=82 width=12) (actual time=11.189..373.041 rows=66 loops=3)"
"                          Filter: (id_person = 28076)"
"                          Rows Removed by Filter: 1291777"
"                    ->  Index Scan using i_g_type on significant_mg mp  (cost=0.43..518.83 rows=19377 width=20) (actual time=0.041..11.846 rows=19454 loops=198)"
"                          Index Cond: (id_g_type = mc.id_g_type)"
"                          Filter: (id_person <> 28076)"
"                          Rows Removed by Filter: 1"
"Planning Time: 0.498 ms"
"Execution Time: 2893.309 ms"

Is there any way I could improve the query performance?

UPDATED (simplified) query:

SELECT mp.id_person
FROM significant_mg mp
INNER JOIN significant_mg mc on mp.id_g_type = mc.id_g_type
WHERE mp.id_person != 28076 AND mc.id_person = 28076
GROUP BY mp.id_person
HAVING SUM(CASE
          WHEN mc.g1 = mp.g1 or mc.g1 = mp.g2 or mc.g2 = mp.g1 or mc.g2 = mp.g1
          THEN 1
          END
         ) / CAST(COUNT(mp.id_g_type) AS decimal) > 0.97


texts: Cannot not connect to Availability Group Listener

texts:
I have a HADR cluster running on Windows 2012 R2 and SQL Server 2016 with Availability Groups. The services were running under a high level domain account. Due to internal audits, I have been asked to change the user name they are running under to a lower level account.
So I have a 2 node cluster (SERVER1\TEST1 and SERVER2\TEST1) and a listener (DB1). Before I changed the account name I could connect to either SERVER1\TEST1, SERVER2\TEST1 or DB1 from SSMS. Since I changed the account that SQL Server and SQL Server Agent are running as, I can no longer connect to DB1. I get

" The target principal name is incorrect. Cannot generate SSPI
context. (Microsoft SQL Server, Error: 0)".

The eventvwr is clean, and the sql error log has "Started listening on virtual network name 'DB1'. No user action is required." Connecting to SERVER1\TEST1 or SERVER2\TEST1 works fine/
What do I need to look at? What is the minimum security access the new user should have?


texts: Performance tuning of Purging table Data in SQL Server 2016

texts:
I have to Automate the Purging huge volume of data from main table to Archive.
Steps I am following :

I am inserting the data from main table to archive table.

After cross verifying the count in both tables , deleting the data from main table.

Risk: Delete statement may go in hung state and DB will go in recovery mode.
Note: we can't perform any DDL operation on main table as it's live transaction table. We can go with DML.

Database: SQL Server 2016
OS: Windows and IIS


texts: What is the best query to use to monitor a SQL Server database's status?

texts:
I want to be able to run a query to get the crucial information about a database's status. I.e., I want the query to be able to tell what whether or not the database is in a good state.

This is the query that I inherited for this check:

SELECT name AS [SuspectDB],
  DATABASEPROPERTY(name, N'IsSuspect') AS [Suspect],
  DATABASEPROPERTY(name, N'IsOffline') AS [Offline],
  DATABASEPROPERTY(name, N'IsEmergencyMode') AS [Emergency],
  has_dbaccess(name) AS [HasDBAccess]
FROM sysdatabases
WHERE (DATABASEPROPERTY(name, N'IsSuspect') = 1)
   OR (DATABASEPROPERTY(name, N'IsOffline') = 1)
   OR (DATABASEPROPERTY(name, N'IsEmergencyMode') = 1)
   OR (has_dbaccess(name) = 0)

If that query returns any results, the assumption being made is that the database is in a suspect or potentially bad state.

Is there a better way to do this?


texts: In shard cluster, if 2 replica set nodes are offline leaving only 1 secondary..where do writes go?

texts:
This is probably a simple question that i can't get definitive answer too.

If i have 10 shards, each being a 3 node replica set. If i loose 2 servers in a replica set causing the primary to change to a read-only secondary. Where does the mongos write new chunks to that the hash key would have sent to this shards primary?

Does the config server detect the shard being read only and inform the mongos that writes should be redirected to another shard?

Also, assuming the above is correct..when the shard gets fixed and has a primary again. Will the chunks be re-balanced to it?

thank you
fLo