texts: Should Fileshare be part of Cluster resource in multisubnet AAG?

texts:
We are planning to expand an existing AAG setup which is a 2 node setup in DC and will be adding a 3rd node in San Jose(different subnet). The current setup is configured with Node and File Share majority. I was going through a Microsoft Whitepaper for AAG setups and want to have a clear idea about this comment which was mentioned

"Because the third node is in a separate data center and no longer has a vote, you should change this quorum model to Node and File Share Majority. Create a file share in the primary data center on a server node that will not be participating in the WSFC"

The current file share witness can actually be seen in the cluster core resources. Does the above sentence now mean that the file share shouldn't be  a part of it? Please clarify


texts: How to do a mysqldump with a use database in the dump

texts:
So the command I normally use to get a database sent to another server is:
mysqldump -u user -p --add-drop-tables database *file*

But I always have to go into the file and add a line at the top:
use database;

So that I use the file to import to a different server. Is there a flag in mysqldump to automatically add the use database?


texts: SQL Server secondary replica snapshot with Basic Availability Group

texts:
I have the following scenario:

I created an HA group of two nodes in SQL Server 2016 Standard (which is limited to two nodes, one database per group and a non readable secondary replica).
I tried to take a snapshot of the secondary non-readable replica, but it's didn't work and I can do that only on primary replica.

On failover snapshot replication (local publication) is moving automatically on primary replica.

From what I read it's possible to have a snapshot on secondary non-readable replica.

What am I doing wrong?


texts: Aggregating high volumes of data from many MySQL servers

texts:
I have 4 servers of MySQL, each receives around 4 million records a day.

I need to aggregate all the inserted records into another main server after doing some mapping processing to the aggregated records. so this new server should receive around 16 million records per day.

I also need to make this operation as real time as possible, so I was thinking of a script running every minute or so that do the sync. or perhapse some other sync tool or mechanism you can suggest.

So, may question is:

Is MySQL the right choice for a 16-20 million records/day ? or I should try something else (MongoDB, Elasticsearch, .. etc)
What tool/language to give the best performance in such task?

Thank you.


texts: Materialized view logs — with condition?

texts:
I've created materialized view logs on a WORKORDER table in an Oracle 19c database.
create materialized view log on my_workorder_system.workorder with rowid;

The plan is to integrate the WORKORDER records to a separate GIS system (Oracle 18c) via a materialized view.
The catch:
The WORKORDER table has both spatial and non-spatial workorder records in it (ISGIS = 0 or 1). I only need to sync the records to the GIS datdabase WHERE ISGIS = 1.
If I understand correctly, my materialized view in GIS will tell the MV logs in the workorder system to purge the log records after they're synced. However, the sync will only ever happen for workorders where ISGIS=1. The workorders where ISGIS=0 will never get synced — and therefore are not needed in the MV logs.
Is there a way to only generate MV logs for records that meet a condition?
For example:
create materialized view log on my_workorder_system.workorder with rowid where ISGISS=1.


texts: Convert the IP Address range to two BIGINT for faster lookup

texts:
I am working on a project in which we need to do IP Address lookups. So for this we have got the datasets which will look like below and now the next steps is to populate this datasets in to the MySql table. Below is the dataset example containing columns-

ip_address      country region  city           metro-code   latitude    longitude   postal-code   country-code   region-code    city-code   continent-code  country-code

24.32.116.116   usa       tx    clarksville    623           33.6103    -95.0498    75426            840           44             34918            6            us
65.29.108.232   usa       mi    novi           505           42.4637    -83.4604    48375            840           23             4339             6            us
66.169.99.69    usa       tx    ft worth       623           32.7016    -97.3823    76109            840           44             771              6            us
72.70.100.111   usa       ma    peabody        506           42.5332    -70.9726    1960             840           22             1392             6            us
174.252.116.137 usa       va    oakton         511           38.8892    -77.3222    22124            840           47             3860             6            us
68.55.144.242   usa       md    pasadena       512           39.1276    -76.5125    21122            840           21             4358             6            us
174.252.83.27   usa       pa    lancaster      566           40.0459    -76.3542    17603            840           39             333              6            us
174.78.192.31   usa       ga    warner robins  503           32.5958    -83.6384    31088            840           11             5052             6            us
98.192.146.235  usa       fl    panama city    656           30.1804    -85.5598    32404            840           10             671              6            us
71.192.181.20   usa       ma    springfield    543           42.1187    -72.5483    1109             840           22             967              6            us
76.183.54.227   usa       tx    dallas         623           32.7202    -96.6769    75217            840           44             77               6            us
69.62.143.140   usa       ca    granite bay    862           38.7442    -121.191    95746            840           5              49451            6            us
69.142.76.32    usa       nj    penns grove    504           39.707     -75.4467    8069             840           31             2335             6            us
70.173.54.93    usa       nv    las vegas      839           36.2059    -115.225    89108            840           29             173              6            us
98.242.8.222    usa       ca    fresno         866           36.7968    -119.883    93722            840           5              19               6            us

Problem Statement:-

I am planning to store the START_IP_NUM and END_IP_NUM as BIGINT in the MySql database instead of storing IP Address in the table as doing a lookup against a BIGINT is far faster than searching a string (dotted ip notation).

So my question is we will be having separate Java Program that will populate the above datasets in the MySql table. So I need to device some sort of logic which can convert the above datasets into like below-

start_ip_num
end_ip_num
country
region
city
metro-code
latitude
longitude
postal-code
country-code
region-code
city-code
continent-code
country-code

I am confuse given a IP Address how should I device start_ip_num and end_ip_num here and store it into the MySql table


texts: Alternative to RDBMS: Modeling Exam/Testing data

texts:
I'm a born and bred relational database guy and C# programmer dude. IBM DB2 & VB.Net in school. TSQL & C# at work.

I'm planning a project for fun: Designing a Testing platform that goes from DB through application/website.

When you think of "Exam" or "Test" Questions/Answers, there are many question types (not my picture, but good example of the multitude of question types):

I want:

Test (ex: Comptia A+)
Classification of question (This question meets objective or domain X of test Y. Test Y needs 3 questions for domain X.)
Multiple question types (not necessarily limited to those in the picture above)
Notes about correct/incorrect answers (This is why ___ is correct. Reference link here.))
History/reversioning

I know I can do all of this in Sql Server, but I don't feel as if it would be the most efficient choice. I can't bring myself to like options that come to mind: Tables per question type. EAV tables. Embedded data structures of some sort.

I've never felt the need to learn "NoSQL" and was wondering if there are any "platforms" that would be a stronger candidate for this than TSQL? Or should I stick to what I know and do what it takes to make it work in a traditional RDBMS?


texts: Storing summary statistics for a large table

texts:
So I have a large table that stores events triggered by a user. These can be anything from logging on to viewing a profile to messaging another user.

I want to track how many views a user receives.

It's a large table, so I want to avoid doing something like:

SELECT *
FROM events
WHERE event_type = 'profile_view'
AND user_id = ...;

I'm trying to think of a good database driven solution that will allow me to store things like profile views in the last 7,14 and even 30 day periods?


texts: Not able to connect to local MySql database "only" when connected to VPN on VM

texts:
Setup:
MySql installed on my Mac using Xammp
VM installed using VMware Fusion running win XP.

I am able to connect to the mysql database through my VM normally.

As soon as I connect to a VPN it stops connecting to the Mysql.

Please let me know how can I make it work so that it connects to Mysql no matter if VPN is ON or OFF.


texts:
I'm wondering if any of you sql buffs out there have any suggestions for a high speed mySQL server. I need something that can handle roughly 33 updates and 33 queries per second, per user.

and for now I really only need to handle say, 10 users.

I know it's hard to say because it depends on the statements so maybe if I supply them it will be of help.

each user is calling :

33x per second

SELECT Online, Xloc, Yloc FROM accounts WHERE AccountID = " + getlocI

and polling:

33x per second

UPDATE `accounts` SET `Online`='"+InitialStat.Onlineint+"', `Xloc`='"+Game.currentx+"',
`Yloc`='"+Game.currenty+"' WHERE (`AccountID`='"+UserVerify.clientID+"') LIMIT 1

with a larger UPDATE only once per minute:

UPDATE `accounts` SET `Online`='"+InitialStat.Onlineint+"',`Zone`='"+Game.currentzone+"',`Xloc`='"+Game.currentx+"', `Yloc`='"+Game.currenty+"', `Level`='"+InitialStat.PlayerLevel+""
                        + "', `XP`='"+InitialStat.XP+"', `Health`='"+InitialStat.health+"', `Magic`='"+InitialStat.magic+"', `Stamina`='"+InitialStat.stamina+""
                        + "', `Strength`='"+InitialStat.strength+"', `Defense`='"+InitialStat.defense+"', `Vitality`='"+InitialStat.vitality+""
                        + "', `Agility`='"+InitialStat.agility+"', `Intelligence`='"+InitialStat.intelligence+"', `Dexterity`='"+InitialStat.dexterity+""
                        + "', `Luck`='"+InitialStat.luck+"', `Gold`='"+InitialStat.gold+"' WHERE (`AccountID`='"+UserVerify.clientID+"') LIMIT 1

the larger UPDATE statement isn't too big of a deal since it happens far less frequently.

But the fastest I can get my other ones to go is somewhere around once per second.

Is there any way I can find out a ballpark of what I would need in a server to get those to function as fast as I'd like them to? I'm currently using a VH, I'd rather not go the dedicated server route yet because this is still in development, so spending a few hundred a month of the server is a bit much.

Any help would be greatly appreciated. If you need more info please let me know.


texts: DBCC CHECKDB Results: page ID contains an incorrect page ID in its page header

texts:
DBCC CHECKDB results for 'tablename':

  Msg 8909, Level 16, State 1, Line 1
     Table error: Object ID 1637580872, index ID 1, partition ID 72057596516040704, alloc unit ID 72057594048020480 (type LOB data), page ID (23:853329) contains an incorrect page ID in its page header. The PageId in the page header = (0:96).

  Msg 8928, Level 16, State 1, Line 1
     Object ID 1637580872, index ID 1, partition ID 72057596516040704, alloc unit ID 72057594048020480 (type LOB data): Page (23:853329) could not be processed.  See other errors for details.

  Msg 8965, Level 16, State 1, Line 1
     Table error: Object ID 1637580872, index ID 1, partition ID 72057596516040704, alloc unit ID 72057594048020480 (type LOB data). The off-row data node at page (23:853329), slot 0, text ID 12904397012992 is referenced by page (16:840512), slot 0, but was not seen in the scan.

This is the only table I have a problem with.

As this issue occur when purge some old data so we don't have enough old backup to say clean. Restore option may not possible.

Now we are looking into repair. Is single table repair possible?

Can we rebuild index as problem is with clustered index?


texts: How do i assign a custom id using a trigger using sequence on a table

texts:
i want to modify my current table which is currently generating id's using a trigger from 1 using sequence_name.nextval to something like DON-100 or REC-124 etc.I have seen some examples doing this using functions which modify the values after the trigger performs it's job which looks like overhead how can i modify my current trigger to produce these custom id's and is not too complex.

CREATE TABLE Atendant (
   ID NUMBER(10) PRIMARY KEY,
   NAME  VARCHAR2(25 CHAR) NOT NULL
);

CREATE SEQUENCE  Atendant_SEQ  NOCACHE;

CREATE or replace TRIGGER  Atendant_BI
  BEFORE INSERT ON  Atendant
  FOR EACH ROW
DECLARE
BEGIN
  IF :NEW.ID  IS NULL THEN
    :new.id := "ATN"Atendant_SEQ.nextval;
  END IF;
END;


texts:
I have a database that is running on AWS RDS (MySQl 5.7.22).

Currently, I have one master and 2 replicas running. My binlog format is MIXED. I have plans to change it to ROW based.

I want to change it during runtime (live environment) since it's our main database.

Questions

What things do I have to consider before I change it?
What is the safest way to do it?
I am just wondering if there will be growth of the binlog size when I change it?


texts: Unable to send WHERE parameters to subquery

texts:
I have the following problem (in PostgreSQL in combination with PostGIS):

I have the following nice query who clusters multiple records. Although it uses PostGIS function, my problem is not PostGIS related, so bear with me ;)

SELECT count(*), ST_Centroid(ST_Collect("geom")) AS "geom"
FROM (
    SELECT kmeans(ARRAY[ST_X("geom"), ST_Y("geom")], 5) OVER (), "geom"
    FROM "table"
    WHERE "geom" && ST_GeomFromText("Bounding box") --It's this line
) AS ksub
GROUP BY kmeans
ORDER BY kmeans;

Where "Bounding box" is a polygon.

This gives me a result. So far so good.

I use Geoserver which retrieves the data from the database based on a bounding box. It does so by requesting all the records using the following query:

SELECT "count", "geom" FROM "table" WHERE "geom" && ST_GeomFromText("Bounding Box");

Where "Bounding Box" is again a polygon.

I'm not able to change the structure of this query.
The reason I need Geoserver is because it will convert the results to a different format (GeoJSON).

Is it possible to write a view that sends the WHERE parameters (or the value "Bounding box" to the subquery? It's not possible to leave the WHERE statement outside of the subquery because the subquery has to calculate on the bounding box.
If you put the WHERE statement outside of the subquery, the subquery will calculate the clusters for all the data and then the WHERE statement will limit it to the bounding box. This is not what I want.
Or perhaps somebody knows how to rewrite the query so the WHERE statement from Geoserver will succeed?


texts:
Every time I create a query and run it to create a report I save the query in my Projects folder in case I have to rerun the query in the future or if I have to modify it for any reason but it seems that for some reason a query which I ran sometime in mid June was not saved. I need to see the code I used in the query because I am looking at the report and some numbers do not look right so I need to check the query to see if there was something written incorrectly in the code.

Is there anyway in some logs maybe where I might be able to see the code for the query I ran (and did not save) back in June? This would be AWSOME if I can. Thanks!


texts:
I have a big table referenced by other tables and need to split the table
on several parts.

I create table by CREATE TABLE product_part0 () INHERITS (product);
Move some rows to product_part0 by INSERT INTO product_part0 SELECT *    FROM ONLY product ORDER BY id LIMIT 200;
Now I need to remove duplicates from master-table. Run DELETE FROM ONLY product WHERE id IN (SELECT id FROM product_part0);

With result:

ERROR: update or delete on table "product" violates foreign key constraint
"product_id_refs" on table "searchcache"
DETAIL: Key (id)=(13375) is still referenced from table "searchcache".

So, how to delete duplicate rows?

I made similar question at postgresql maillist.


texts: Display query which SSMS executes from Interface/WYSIWYG edits

texts:
If I am to add a new column to a table in SQL Server Management Studio 2008 via the interface/WYSIWYG (as opposed to writing a SQL query) this will, behind the scenes, create a SQL query which it will execute to perform the requested changes.

Is there any way to capture these SQL queries so that I am able to easily execute them on another server, without using SSMS, or re-writing the query.


texts: How to change array_agg() to return empty array on empty input in PostgreSQL?

texts:
I am trying to write a PostgreSQL array_agg() variant that returns the empty array '{}' instead of NULL. Trying to avoid the performance hit of custom aggregate functions with CREATE AGGRGATE, I tried to re-use the built-in array_agg_transfn and array_agg_finalfn. Starting from the code example in the documentation, an initial version similar to array_agg works (as DB superuser):

CREATE AGGREGATE array_agg_z (anynonarray)
(
    sfunc = array_agg_transfn,
    stype = internal,
    finalfunc = array_agg_finalfn,
    finalfunc_extra
);

And it returns NULL on empty input as expected:

=> SELECT array_agg_z(i) IS NULL FROM (SELECT 0 WHERE 1 = 2) t(i);
 ?column?
----------
 t
(1 row)

Now, to change array_agg_z() to return empty array on empty input, I attempted to set the initcond to '{}' as follows:

CREATE AGGREGATE array_agg_z (anynonarray)
(
    sfunc = array_agg_transfn,
    stype = internal,
    finalfunc = array_agg_finalfn,
    initcond = '{}',
    finalfunc_extra
);

Now it's giving an error:

=> SELECT array_agg_z(i) IS NULL FROM (SELECT 0 WHERE 1 = 2) t(i);
ERROR:  cannot accept a value of type internal

How to work around this?

Or do I have to hack the C source code related to array_agg() to make this variant?

By the way, this is with PostgreSQL 12.


texts: Selecting the last record that meets a condition

texts:
I have a simplified table like this:

id |val |
---|----|
1  |6   |
2  |5   |
3  |4   |
4  |4   |
5  |8   |
6  |9   |
7  |7   |

I want to select the records in a way that where val < 5 the last record where val >= 5 is selected. So the result should look like this:

id |val |
---|----|
1  |6   |
2  |5   |
3  |5   |
4  |5   |
5  |8   |
6  |9   |
7  |7   |

I was wondering if this is possible without defining a function, so I tried:

SELECT
CASE
    WHEN val < 5 THEN LAG(id) OVER (ORDER BY id)
    WHEN val >= 5 THEN id
END id,
CASE
    WHEN val < 5 THEN LAG(val) OVER (ORDER BY id)
    WHEN val >= 5 THEN val
END val
FROM test;

but it only returns:

id |val |
---|----|
1  |6   |
2  |5   |
3  |5   |
4  |4   |
5  |8   |
6  |9   |
7  |7   |

Is is possible to write this query without defining a function? Or would it be better with a function?

EDIT: A combined solution

For the sake of completeness, although it slightly differs from the original problem description, I include that if the answers from @Andriy M and @Hogan are combined then NULL values will be preserved and only values below the threshold will be updated with the last valid value. In some cases it is desirable to keep the NULL values. Thus the query would be like this:

SELECT
    id,
    val,
CASE WHEN val < 5 THEN (SELECT sub.val
                        FROM test AS sub
                        WHERE sub.id <= main.id
                            AND sub.val >= 5
                        ORDER BY id DESC
                        LIMIT 1)
ELSE val END AS newval
FROM test AS main;

Which returns:

id |val |newval |
---|----|-------|
1  |6   |6      |
2  |5   |5      |
3  |4   |5      |
4  |NULL|NULL   |
5  |8   |8      |
6  |9   |9      |
7  |7   |7      |


texts: Restore from .db folder for Analysis Services

texts:
There is an objective to recover cube from long time ago retired MSAS instance. Issue I'm currently standing with is that actual .abf backups are now in a cold storage and recovering them would take a while; however, i do have filesystem backup of Data (as well as logs) folder of filesystem with .db folder (seemingly containing all .dim, .ds and .dsv files).

How feasible it is to attempt to recover cube from said folder rather then proceed with searching for .abf files?


texts: Combine the effects of separate delete and insert rows

texts:
How to I combine the two rows below:

Date          Id   For_Id   Type         New_Value    Old_Value
------------------------------------------------------------------
2015-08-13    6    327      DELETE       NULL         Value1
2015-08-13    6    327      INSERT       Value2       NULL

...into a single row, removing the NULLs, matching rows based on the dates?

Date          Id   For_Id   New_Value    Old_Value
-------------------------------------------------
2015-08-13    6    327      Value2       Value1


texts:
I am working on restructuring the 'database' my company uses, and have hit a bit of a wall on the design of a specific area for the data tables.

I need to setup the data model such that we can create Purchase Orders, store the data for them and track multiple data points on these orders.  These data points include; parts ordered, quantity of each part ordered, price of each part (which can change between quote and the submitted order), the supplier, our contact for the supplier, the associated job, when the order was placed, when the parts were received, and whether or not any parts are on backorder.

As you can see it's a lot of data to spread through some tables.  My first instinct was to come up with the following table layout:

Table: purchase_order
    order_id     INT IDENTITY(1,1)
    order_number VARCHAR(20)
    job_id       INT
    supplier_id  INT
    contact_id   INT
    notes        VARCHAR(MAX)
    PK = order_id

Table: order_status
    status_id          INT IDENTITY(1,1)
    status_description VARCHAR(50)
    PK = order_id

Table order_part
    order_id     INT
    part_id      INT
    status_id    INT
    ordered      INT
    received     INT
    log_date     DATE
    user_list_id INT
    notes        VARCHAR(MAX)
    PK = order_id, part_id, status_id

Table: order_part_price
    log_id        INT IDENTITY(1,1)
    order_id      INT
    part_id       INT
    part_price_id INT
    conversion_id INT
    log_date      DATE
    PK = log_id

With this setup, I can do the things I mentioned above, though I can definitely see some flaws with it.  One of the things I am concerned with at the moment is with concerns to tracking parts ordered and parts received.  The way it is set up above, when parts are received, the order_parts table would get a new entry, with the received column containing how many parts have been received.  Outside of that and the change in the status from Ordered to Received or Backordered that rest of the row's data would be the exact same as the data for the initial entry when the order was made.  As such, I've thought up the following revised table setup:

Table: purchase_order
    order_id    INT IDENTITY(1,1)
    order_num   VARCHAR(20)
    job_id      INT
    supplier_id INT
    contact_id  INT
    status_id   INT
    PK = order_id

Table: order_part
    order_id     INT
    part_id      INT
    quantity     INT
    log_date     DATE
    user_list_id INT
    PK = order_id, part_id

Table: shipment
    order_id     INT
    part_id      INT
    received     INT
    log_date     DATE
    user_list_id INT
    PK = order_id, part_id

Essentially I am splitting the old order_part table into two separate tables, one that deals with ordering parts, and one that deals with receiving parts.  I am also moving the status identifier to the purchase_order table since the status is part of the whole order, even if individual parts are the ones being backordered.

Now my question is, does this change make sense?  What are some other obvious flaws in my original design that I am missing?  Does my new design give me some problems I didn't have before?

This is the first large scale Database project I have worked on so I am trying to make it as best as I can possibly manage.  Thanks for your time.


texts: Group_by and having vs Joins when table is self joined 5-6 times

texts:
Now the situation due to which this question came to me.
Schema is

    Table User_Read_Book
       user_id | book_id

Now I want to get users who have read certain books. Say give me users who have read book number 1 and 2. Number of books to test can go upto 10.

First query I wrote :

Select user_id from User_Read_Book Where book_id In (1,2) Group by user_id Having count(book_id) = 2

Second query:

Select  user_id from User_Read_Book as U join User_Read_Book as U1 On
 U.user_id = U1.user_id And U1.book_id = 1 where U.book_id = 2

As said in this answer https://stackoverflow.com/a/621891 that prefer joins in case of group by and having I did the second query.

But my question is which is better query when number to match up are large. Say when you have to find users who have read 7 books

   Having Count(book_id) = 7
   or
   6 joins to the same table.

I know this question is best answered when tested on large and real time data.
Still what is the opinion of experts on this?


texts: PostgreSQL and MONEY data type for currency values

texts:
For a project, I created a table with a column price MONEY NOT NULL column. And I thought it would handle decimals properly, unlike a floating number (i.e. IEEE rounding issues), but I end up having values like $9.94 (string) being returned from the database, instead of 9.94 (numeric). Having to manually manipulate the field value by removing the dollar sign is silly; is there a way to have a MONEY column without a currency sign?

If not, what's the best data type replacement to handle currency values?


texts: Why does this scalar function not raise an error?

texts:
My understanding is that scalar functions always return a row, but I don't understand why the first of these functions does not raise an exception while the second does. The only difference is the second accepts an additional parameter.
The first one always returns a row of NULL.
create schema app_testbed;

create table app_testbed.items(
    id serial primary key,
    name varchar
);

-- why does the "other" parameter change how it works?
create or replace function app_testbed.this_should_break_but_doesnt(
  item app_testbed.items,
  other text default null
)
returns app_testbed.items as $$
  declare
    v_item app_testbed.items;
  begin
    raise exception 'Does not raise.';
  end;
$$ language plpgsql strict volatile;

-- without the additional parameter it raises as expected
create or replace function app_testbed.this_breaks(
  item app_testbed.items
)
returns app_testbed.items as $$
  declare
    v_item app_testbed.items;
  begin
    raise exception 'Raises.';
  end;
$$ language plpgsql strict volatile;


texts: SQL 2000 - tsql to find out failed jobs in last 24 hours

texts:
I want to find out which sql agent jobs failed in last 24 hours...
How can I find it using TSQL for SQL 2000?

Regards
Manjot


texts: What happen with the Log backup if I set the SQL Server clock back?

texts:
What happen with the Log backups if I set the SQL Server clock back? In countries with daylight saving this happens at least once a year. Will the point in time restore with the STOPAT option fail?

Using fn_dump_dblog I see this after setting the clock back two hours.


texts: How do you generate the DDL that created a DB2 LUW database?

texts: There are various options for db2look you can use to see the DDL for just about every object in the database except the command that created the database itself.  Is there any way with db2look, or perhaps with some other system command, where you can see the exact command structure that created a database?


texts: DBF file exist while user has been dropped?

texts:
I am new to Oracle. I have dropped some users from my database using:

DROP USER username cascade;

The issue is that the .dbf file still exists in the $ORACLE_HOME/dbs/oradata/ directory, which is creating disk space issues.

Can I remove the unused file directly or by some other processes? Also the size of the .trc file has increased to 16 GB. How can I clean that up?

I can delete these file directly but not sure that it may cause any start up or configuration issue, which I don't want.

Simply my concern is to get rid of disk space issue from the above unwanted files.


texts:
I'm trying to install Oracle client 12.1.0.2.0 (32-bit) on a completely clean copy of windows Server 2012, and I'm hitting a problem.

The installer runs fine until it gets to the final part - the net configuration assistant. An error message appears and says "[INS-20802] Oracle Net Configuration Assistant failed.". Here's the relevant part of the log file that is generated (I can post the entire thing if requested, but here's the bit with the actual error occurs):

INFO: Read: UnsatisfiedLinkError exception loading native library: oranjni12
INFO: Stderr Read: java.lang.UnsatisfiedLinkError: oracle.net.common.NetGetEnv.jniGetOracleHome()Ljava/lang/String;
WARNING: Skipping line: UnsatisfiedLinkError exception loading native library: oranjni12
INFO: Read: java.lang.UnsatisfiedLinkError: C:\app\ORACLE_USER\product\12.1.0\client_1\BIN\oranjni12.dll: Can't find dependent libraries
INFO: Stderr Read:  at oracle.net.common.NetGetEnv.jniGetOracleHome(Native Method)
INFO: Stderr Read:  at oracle.net.common.NetGetEnv.getOracleHome(Unknown Source)
INFO: Stderr Read:  at oracle.net.ca.NetCALogger.getOracleHome(NetCALogger.java:230)
INFO: Stderr Read:  at oracle.net.ca.NetCALogger.initOracleParameters(NetCALogger.java:215)
INFO: Stderr Read:  at oracle.net.ca.NetCALogger.initLogger(NetCALogger.java:130)
INFO: Stderr Read:  at oracle.net.ca.NetCA.main(NetCA.java:433)
WARNING: Skipping line: java.lang.UnsatisfiedLinkError: C:\app\ORACLE_USER\product\12.1.0\client_1\BIN\oranjni12.dll: Can't find dependent libraries
INFO: Read:
WARNING: Skipping line:
INFO: Read: Error: oracle.net.common.NetGetEnv.jniGetOracleHome()Ljava/lang/String;
WARNING: Skipping line: Error: oracle.net.common.NetGetEnv.jniGetOracleHome()Ljava/lang/String;
INFO: Read: Oracle Net Services configuration failed.  The exit code is 1
WARNING: Skipping line: Oracle Net Services configuration failed.  The exit code is 1
INFO: Completed Plugin named: Oracle Net Configuration Assistant

Here are a few things I have tried:

turning the firewall off
disconnecting from network (clutching at straws!)
running the installer as administrator
re-downloading the client installer from Oracle, in case it had somehow been corrupted

None of these things made any difference. I have successfully installed this exact version of the client on a Windows Server 2012 PC in the past and it worked fine...

Has anyone else encountered this issue?


texts: Error: Relation ["table name"] does not exist

texts:
I am trying to create tables based on relational schema, but I get "Relation does not exist" errors for some tables and could not find a way to get out of this. I will display my schema, code, and actual errors below. I really feel this might have a simple solution, but I just got stuck into it..

CREATE TABLE Driver
(
Id           CHAR(11)      NOT NULL,
FirstName    VARCHAR(15)   NOT NULL,
LastName     VARCHAR(20)   NOT NULL,
Phone        CHAR(12)      CHECK (char_length(Phone) > 8),

PRIMARY KEY  (Id)
);

CREATE TABLE Driving
(
Id           CHAR(11)         REFERENCES Driver ON DELETE CASCADE,
BusId        INT              REFERENCES Bus    ON DELETE CASCADE,

PRIMARY KEY  (Id, BusId)
);

CREATE TABLE Bus
(
BusId    INT           NOT NULL,
Make     VARCHAR(20)   DEFAULT ('Volvo'),
Model    VARCHAR(20),
Year     SMALLINT      NOT NULL    CONSTRAINT ManufacturingYear
                                         CHECK ((Year > 1970) AND
                                                (Year < date_part('year', current_date) + 1)),
 Space   SMALLINT      CHECK (Space > 0),
 Route   SERIAL,

 PRIMARY KEY (BusId),
 FOREIGN KEY (Route) REFERENCES Route ON DELETE SET NULL
 );

CREATE TABLE Route
(
RouteId          SERIAL         NOT NULL,
StartingPoint    SMALLINT       NOT NULL,
EndingPoint      SMALLINT       NOT NULL,

PRIMARY KEY(RouteId),
FOREIGN KEY (StartingPoint) REFERENCES Stop,
FOREIGN KEY (EndingPoint) REFERENCES Stop
);

CREATE TABLE RouteStops
(
Route       SERIAL,
Stop        SMALLINT,

PRIMARY KEY(Route, Stop),
FOREIGN KEY (Route)  REFERENCES Route  ON DELETE CASCADE,
FOREIGN KEY (Stop)   REFERENCES Stop   ON DELETE CASCADE
);

CREATE TABLE Stop
(
StopId    SMALLINT      NOT NULL,
Name      VARCHAR(30)   DEFAULT ('Station'),

PRIMARY KEY(StopId)
);

Errors:

ERROR:  relation "bus" does not exist
ERROR:  relation "route" does not exist
ERROR:  relation "stop" does not exist
ERROR:  relation "route" does not exist


texts:
In this excellent article, Paul White demonstrates branches in parallel plans.

https://sqlperformance.com/2013/10/sql-plan/parallel-plans-branches-threads

At the most fine grained level, we can look at tasks using sys.dm_os_tasks

Is there a way of relating tasks to a branch?
Is there a way of determining the count of branches for a current request?


texts: SQL Server FileTables (File Stream) file Corruption

texts:
We're running an implementation of SQL Server 2012 FileTables with several scientific instruments with PCs that write data directly to a mapped drive of a FileTables share. The size of this share is now about 60GB.

Twice in the past six months, I've discovered backups failing. I've only been able to solve this by running chkdsk /f with the volume dismounted.

After running chkdsk this last time, we started seeing a large number of files showing up as having 0 bytes when we downloaded them. However, in the File Explorer (and in the cached_file_size column in SQL Server) the file still has size, yet the file_stream column shows 0x.

To add to the crazy, passing the 0x file_stream column to the DATALENGTH() returns the cached_file_length.

I'm at a loss to how to proceed -- I need to restore these files, but I can't even find them efficiently because the DATALENGTH() function is lying.

So the questions:

How would you go about finding these files efficiently?

SELECT stream_id
 FROM dbo.FileTable
 WHERE file_stream = 0x

is far too slow.
How would you attempt to troubleshoot the issues described above?

EDIT
SQL Server version 11.0.3128
SQL Error logs look normal.
Windows error logs are reporting problems like the following:

  The operating system returned the error '2(The system cannot find the file specified.)' while attempting 'FsDoHandler::CreateFile' on 'h:_production_db\UploadFiles\264759d7-159c-4dbb-b8b4-a52b2ab06153\be030672-3a28-4067-a621-4e1f653ced40\000ce69b-00000801-0030' at 'fstrman.cpp'(2075).

The error that causes the backups to fail (and for me to run CHKDSK) is:

  The operating system returned the error '1392(The file or directory is corrupted and unreadable.)' while attempting 'FindNextFile' on 'h:_production_db\UploadFiles\264759d7-159c-4dbb-b8b4-a52b2ab06153\be030672-3a28-4067-a621-4e1f653ced40'.

Thanks for help!
Dan.

Edit 2:
It seems that I can loop through all of the files using .NET to open & close without changing in read-write mode. This seems to update whatever metadata that Windows and SQL Server are using to provide the DATALENGTH and cached_file_size values.


texts: How do I Order By on multiple rows in Postgresql

texts:
I have a table with columns with severities like:
name   | critical | major | trivial
----------------------------------------
Apache |2         |3      |2
Eclipse|5         |0      |4
TestNg |0         |0      |5
JUnit  |0         |2      |7

I want to list the rows in the order by their severities in descending order - critical, major and trivial.
For the above example, I'd like to get back:
name  | critical | major | trivial
----------------------------------------
Eclipse|5         |0      |4
Apache |2         |3      |2
JUnit  |0         |2      |7
TestNg |0         |0      |5

critical has highest severity, then comes major and then trivial. Notice eclipse has to come first since 5 > 2 (Critical, internal sorting)


texts: MySQL Error Code 3019 (Undo Error Log, no space left)

texts:
I'm trying to remove some data to get some extra free space, but running the "DELETE FROM table ..." query gives me this error:

Error Code: 3019 Undo Log error: No more space left over in system
tablespace for allocating UNDO log pages. Please add new data file to
the tablespace or check if filesystem is full or enable auto-extension
for the tablespace

I found this:
https://stackoverflow.com/questions/42628687/undo-log-error-no-more-space-left-over-in-system-tablespace-for-allocating-undo
The recommend to run this query:
ALTER UNDO TABLESPACE tablespace_name SET INACTIVE;

But it gives me an Error Code: 1064
MySQL version: 5.7.31


texts: How to better perform a query split into subqueries in PHP

texts:
In PHP I have made this query:

$getTrackID = $this->db->query("SELECT `id`,`uid` FROM `tracks`, `users` WHERE `time` > CURDATE() - interval 1 year AND `uid`=`idu` AND `public` > 0 ORDER BY RAND() LIMIT 10");
$TrackID = $getTrackID->fetch_assoc();
$id=$TrackID['id'];
$xid=$TrackID['uid'];

The problem is that it takes 1 sec to return the result. I saw this question and it seems better practice split big queries into subqueries.

Is this the case? To better perform should I need to use something like:

$getTrackTime = $this->db->query("SELECT `id`,`uid` FROM `tracks`, `users` WHERE `time` > CURDATE() - interval 1 year AND `uid`=`idu`");
// Declare the rows array
$rows = array();
while($row = $getTrackTime->fetch_assoc()) {
    // Store the result into the array
    $rowsTime[] = $row;
}

$getTrackPublic = $this->db->query(sprintf("SELECT `%s`,`%s` FROM `tracks`, `users` WHERE `public` > 0",$rowsTime['id'],$rowsTime['uid']));
$rows = array();
while($row = $getTrackPublic->fetch_assoc()) {
    $rowsPublic[] = $row;
}
...


texts: Export a varbinary column to file system using existing authentication

texts:
I want to export a varbinary column to a file system. I can use BCP command to export data from a varbinary column, which requires username and password being sent along with BCP command. I can use xp_cmdshell to execute the BCP command too.

I usually connect to a SQL server using SSMS using SQL authentication(has sysadmin permission), so I need to pass user name and password along with bcp statement in SSMS while running query even though I have been connected to SQL server.

So how I can I easily export a varbinary column to a file system using a query using existing SQL authentication?


texts: Post Mortem Analysis: PostgreSQL replication failed

texts:
We have a PostgreSQL 9.4.9 production server which was replicating to a slave instance, but today I discovered that the instance is out of sync!

The obvious actions would be to recreate the slave, setup metrics and proper alarms for the replication activity, so we can effectively monitor the sync status between master and slave nodes.

But, as the sync failed, I'd like to first diagnose the problem and try to identify the root cause of it, as this would be the second time this happens in about 6 months.

Question: How to diagnose what failed in the replication process so it can be done in a better way this time?

Version specifics:

PostgreSQL 9.4.9 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

From slave node, at /var/log/postgresql/postgresql-9.4-main.log I can see:

2017-07-18 19:43:55 UTC [12816-1] LOG:  started streaming WAL from primary at 125D/68000000 on timeline 1
2017-07-18 19:43:55 UTC [12816-2] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000125D00000068 has already been removed

2017-07-18 19:44:00 UTC [12817-1] LOG:  started streaming WAL from primary at 125D/68000000 on timeline 1
2017-07-18 19:44:00 UTC [12817-2] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000125D00000068 has already been removed

2017-07-18 19:44:05 UTC [12821-1] LOG:  started streaming WAL from primary at 125D/68000000 on timeline 1
2017-07-18 19:44:05 UTC [12821-2] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000125D00000068 has already been removed

2017-07-18 19:44:10 UTC [12825-1] LOG:  started streaming WAL from primary at 125D/68000000 on timeline 1
2017-07-18 19:44:10 UTC [12825-2] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000125D00000068 has already been removed

2017-07-18 19:44:15 UTC [12826-1] LOG:  started streaming WAL from primary at 125D/68000000 on timeline 1
2017-07-18 19:44:15 UTC [12826-2] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000125D00000068 has already been removed

New Question: How can I see backward on where the actual problem appeared?

Master postgresql.conf: https://pastebin.com/NJX5ku6m

Slave postgresql.conf: https://pastebin.com/CUZcyazC

Slave recovery.conf:

standby_mode = on
primary_conninfo = 'host=10.1.1.65 port=5432 user=replicador password=replicador'


texts:
I am in charge of a large PostgreSQL database, with few dozen tables. I suspect that many of these tables are never accessed.

What's the best way to check when was the last time a certain table was accessed? I thought of adding a trigger on DELETE, INSERT and UPDATE, but I hope there is a more efficient way.


texts:
I have following query coming up in mysql-slow log, however i am not sure how to optimized this query and make it much faster. Any ideas on this ?
I have already used indexes in following in the user tables and other tables. however its still not right. All my tables are in InnoDB table types.

SELECT  count(*) as total
    FROM  users_interest
    WHERE  user_to='64500'
      AND  new='Y'
    UNION  ALL
SELECT  count(*)
    FROM  users_view
    WHERE  user_to='64500'
      AND  new='Y'
    UNION  ALL
SELECT  count(*)
    FROM  mail_msg
    WHERE  user_to='64500'
      AND  folder='1'
      AND  new='Y'
    UNION  ALL
SELECT  count(*)
    FROM  user
    WHERE  activated='Y'
      AND  mode NOT IN('suspended','hellban','errorban','deleted','hidden' )
      AND  (gender!='M')
      AND  user_id NOT IN (
        SELECT  user_from
            FROM  users_block
            WHERE  user_to='64500' )
      AND  user_id NOT IN (
        SELECT  user_from
            FROM  users_block
            WHERE  user_from='64500' )
      AND  online=1
    UNION  ALL
SELECT  count(*) as total_recent
    FROM  user
    WHERE  activated='Y'
      AND  mode NOT IN('suspended','hellban','errorban','deleted','hidden')
      AND  (gender!='M')
      AND  user_id NOT IN (
        SELECT  user_from
            FROM  users_block
            WHERE  user_to='64500' )
      AND  user_id NOT IN (
        SELECT  user_from
            FROM  users_block
            WHERE  user_from='64500' )
      AND  last_visit >= DATE_SUB(NOW(), INTERVAL 60 MINUTE);

# Time: 160124 22:46:38
# User@Host: xyz_user[xyz_user] @ localhost []  Id: 194710
# Schema: xyz_dev  Last_errno: 0  Killed: 0
# Query_time: 10.199606  Lock_time: 0.000048  Rows_sent: 81  Rows_examined: 2358518  Rows_affected: 0
# Bytes_sent: 4158
SET timestamp=1453655798;


texts: Don't know how to transform variable entity into relational table

texts:
INTRODUCTION AND RELEVANT INFORMATION:
The following example illustrates the problem I face:
Animal has a race, which can be a cat or a dog. Cat can be either Siamese or Persian. Dog can be a German shepherd or Labrador retriver.
Animal is a strong entity, while its race is an attribute that can have one of the two offered values ( cat or a dog ). Both these values are complex ( I have added here only the type of dog/cat to illustrate the problem, but there can also be the cat's / dog's name and bunch of other stuff ).
PROBLEM:
I don't know how to create relational tables for this example.
MY EFFORTS TO SOLVE THE PROBLEM:
I have tried to draw ER diagram, using Chen's notation, that represents the problem but being a beginner I don't know if I did it right. Here is what I have got:

I apologize if I drew something wrong, please correct me if that is the case. I don't wish to simply get "free solution" but also to learn how to deal with this problem so I can solve it on my own in the future.
The only thing that comes to my mind is to create two separate tables, one for cats and one for dogs. Also, the race attribute in the Animal table would only store cat or a dog value. Something like this:
Animal< # Animal_ID, race, other attributes >
Cat < # Cat_ID, $ Animal_ID, breed >
Dog < # Dog_ID, $ Animal_ID, breed >

I really have a bad feeling about my solution and I fear it is wrong, hence the below question.
QUESTIONS:

How can I transform my example into ER diagram ?
How to transform that ER diagram into relational tables?

If further info is required leave a comment and I will update my post as soon as possible. Also feel free to add appropriate tags since I am fairly new here.
Thank you.


texts: Will a WHERE query do checks on simpler comparisons (i.e. bit) before running more arduous comparisons (i.e. varchar)?

texts:
If I write a query that includes a compound WHERE clause, e.g.:

SELECT *
FROM MyTable
WHERE BitField = 1
    AND VarcharField = 'asdf'

and the inclusion of that bit comparison simply excludes the same fields that the varchar comparison will exclude, will the presence of that bit field comparison render me a performance improvement?


texts:
I am running the following query to get list of all PDBs using SQLPLUS and calling each PDB NAME using for loop in shell script.
Unfortunately, few PDB NAMEs are split/broken in the output(for example, P  DB1P016, PD B1P032 etc) so the variable is not assigning proper/correct value.
Any idea to fix this?

SQL> select LISTAGG(a.name, ' ') WITHIN GROUP (ORDER BY name) as pdbs from v$pdbs a;

PDBS
-------------------------------------------------------------------------------------------------------------------------------------------------
IOC1P002 PDB$SEED PDB1P001 PDB1P002 PDB1P003 PDB1P004 PDB1P005 PDB1P006 PDB1P007 PDB1P008 PDB1P009 PDB1P010 PDB1P012 PDB1P013 PDB1P014 PDB1P015 P
DB1P016 PDB1P017 PDB1P018 PDB1P019 PDB1P020 PDB1P021 PDB1P022 PDB1P023 PDB1P024 PDB1P025 PDB1P026 PDB1P027 PDB1P028 PDB1P029 PDB1P030 PDB1P031 PD
B1P032 PDB1P033 PDB1P034 PDB1P035 PDB1P036 PDB1P037 PDB1P038 PDB1P039 PDB1P040 PDB1P041 PDB1P042 PDB1P043 PDB1P044 PDB1P045 PDB1P046 PDB1P047 PDB
1P048 PDB1P049 PDB1P050 PDB1P051 PDB1P052 PDB1P053 PDB1P054 PDB1P055 PDB1P056 PDB1P057 PDB1P058 PDB1P059 PDB1P060 PDB1P061 PDB1P062 PDB1P063 PDB1
P064 PDB1P065 PDB1P066 PDB1P067 PDB1P068 PDB1P069 PDB1P070 PDB1P071 PDB1P072 PDB1P073 PDB1P074 PDB1P075 PDB1P076 PDB1P077 PDB1P078 PDB1P079 PDB1P
080 PDBCUS12


texts:
I'm preparing to use the FileTable feature in SQL Server 2012, but I'm unclear about how backups work with this feature:

Does a full database backup include FileTable files?
How would I perform the restore of a single file, for example?


texts:
I ran into some trouble modeling an electrical schematic in SQL.
The structure I'd like to capture is

  part ←────────── pin
   ↑                ↑
part_inst ←───── pin_inst

where "inst" is short for "instance".

For example, I might have as a part an LM358 op-amp with pins
1OUT, 1IN-, 1IN+, GND, 2IN+, 2IN-, 2OUT, and VCC. I might
then place this part on a schematic, creating a part_inst and 8
pin_insts.

Ignoring data fields, my initial attempt at a schema was

create table parts (
    part_id bigserial primary key
);
create table pins (
    pin_id bigserial primary key,
    part_id bigint not null references parts
);
create table part_insts (
    part_inst_id bigserial primary key,
    part_id bigint not null references parts
);
create table pin_insts (
    pin_inst_id bigserial primary key,
    part_inst_id bigint not null references part_insts,
    pin_id bigint not null references pins
);

The main problem with this schema is that a pin_inst might be tied
to a part_inst with part_id=1 but its pin has part_id=2.

I'd like to avoid this problem on the database level rather than the
application level. So, I modified my primary keys to enforce that.
I marked the changed lines with --.

create table parts (
    part_id bigserial primary key
);
create table pins (
    pin_id bigserial,                                          --
    part_id bigint not null references parts,
    primary key (pin_id, part_id)                              --
);
create table part_insts (
    part_inst_id bigserial,                                    --
    part_id bigint not null references parts,
    primary key (part_inst_id, part_id)                        --
);
create table pin_insts (
    pin_inst_id bigserial primary key,
    part_inst_id bigint not null,                              --
    pin_id bigint not null,                                    --
    part_id bigint not null references parts,                  --
    foreign key (part_inst_id, part_id) references part_insts, --
    foreign key (pin_id, part_id) references pins              --
);

My gripe with this method is that it pollutes the primary keys:
Everywhere I refer to a part_inst, I need to keep track of both the
part_inst_id and the part_id. Is there another way I can go about enforcing the constraint
pin_inst.part_inst.part_id = pin_inst.pin.part_id without being overly verbose?


texts:
I need to delete many rows from one table persons, that is referenced by many other tables as FK.

If I had DELETE CASCADE, I could just delete the records from persons, and automatically delete from the other tables, but I don't like the idea.

I would like a script that takes as input a DELETE statement, and outputs all the DELETE statements that are needed. Ideally, it would tell me how many records would be deleted.

Example:

Input:

DELETE FROM persons WHERE person_id < 1000000

Output:

-- This would delete 124,345 records
DELETE FROM persons_addresses WHERE person_id < 1000000
-- This would delete 82,954 records
DELETE FROM persons_phone numbers WHERE person_id < 1000000
...
-- This would delete 999,999 records
DELETE FROM persons WHERE person_id < 1000000


texts: Run calculation not working in sub query, "does not exist"

texts:
PostgreSQL 13.2 (Debian 13.2-1.pgdg100+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

I have a query that provides the count of 2 things over 2 time periods:

time period of this week, start of the week (Sunday/Monday midnight)
-> today
time period of last week, start of last week (Sunday/Monday midnight)
-> today but last week

What I would like is to also perform a calculation on the two "car" count ints that are returned and provide this with the query result.
I can of course do this in the consuming app, but I'd love to be able to do it in my SQL.
Working SQL
This is the base SQL and is what I currently have and working:
WITH last_week AS (
  SELECT COUNT(car_name) as car_count_last_week
  FROM car_store
  WHERE car_name = 'awesome'
  AND car_time >= date_trunc('week', CURRENT_DATE - INTERVAL '1 week')
  AND car_time <= CURRENT_DATE - INTERVAL '6 days'
), current_week AS (
  SELECT COUNT(car_name) AS car_count_current_week
  FROM car_store
  WHERE car_name = 'awesome'
  AND car_time >= date_trunc('week', CURRENT_DATE)
  AND car_time <= CURRENT_DATE
)
SELECT car_name,
  date_trunc('week', CURRENT_DATE - INTERVAL '1 week') AS start_of_last_week,
  CURRENT_DATE - INTERVAL '6 days' AS today_but_last_week,
  date_trunc('week', CURRENT_DATE) AS start_of_current_week,
  CURRENT_DATE AS today,
  car_count_last_week,
  car_count_current_week
  FROM car_store
  CROSS JOIN last_week, current_week
  WHERE car_name = 'awesome'
  GROUP BY car_name, car_count_last_week, car_count_current_week
  ORDER BY car_name;

Setup DB table
CREATE TABLE IF NOT EXISTS car_store (
     car_id INT GENERATED ALWAYS AS IDENTITY,
     car_time TIMESTAMP NOT NULL,
     car_name VARCHAR(255) NOT NULL,
     PRIMARY KEY(car_id)
  )

INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-03 15:28:00.116594');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-11 16:13:07.217903');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-01 18:03:27.217903');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-14 18:03:27.217903');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-12 18:03:27.217903');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-13 18:03:27.217903');

Thing that causes error
I would like add the following code - to calculate difference_between_weeks:
ROUND(car_count_current_week/(car_count_last_week/100) - 100)
    AS difference_between_weeks

I have tried calculating in the columns, I have tried adding another sub query. But I don't seem to be able to calculate the difference_between_weeks as it tells me "does not exist" or ERROR:  division by zero
Full SQL that errors
An example of the SQL with the added code that errors:
WITH last_week AS (
  SELECT COUNT(car_time) as car_count_last_week
  FROM car_store
  WHERE car_name = 'awesome'
  AND car_time >= date_trunc('week', CURRENT_DATE - INTERVAL '1 week')
  AND car_time <= CURRENT_DATE - INTERVAL '6 days'
), current_week AS (
  SELECT COUNT(car_time) AS car_count_current_week
  FROM car_store
  WHERE car_name = 'awesome'
  AND car_time >= date_trunc('week', CURRENT_DATE)
  AND car_time <= CURRENT_DATE
)
SELECT car_name,
  date_trunc('week', CURRENT_DATE - INTERVAL '1 week') AS start_of_last_week,
  CURRENT_DATE - INTERVAL '6 days' AS today_but_last_week,
  date_trunc('week', CURRENT_DATE) AS start_of_current_week,
  CURRENT_DATE AS today,
  car_count_last_week,
  car_count_current_week,
  ROUND(car_count_current_week/(car_count_last_week/100)) - 100 AS difference_between_weeks
  FROM car_store
  CROSS JOIN last_week, current_week
  WHERE car_name = 'awesome'
  GROUP BY car_name, car_count_last_week, car_count_current_week
  ORDER BY car_name;

Returned error
I get the following error:
ERROR:  division by zero
SQL state: 22012

I feel like I am close but I also feel this may not be possible?
Any pointers to what I am missing would be hugely appreciated.


texts: Does mongodb automatically move small non-partitoned databases to spread read load?

texts:
I have a few hundred small databases, each between 0.2GB and 4.0GB in size. These are sitting on a sharded mongodb environment with 10 or so shards.

At any one time, only a very small subset of these databases are being (intensively) written to.

All of them are being intensively read from, all of the time (Target OTE 300,000 queries per second). I can exert enough control over the read order to spread the reads across the databases fairly evenly.

Right now, none of these small databases are partitioned.

When I look at the output of the db.printShardingStatus() command, I see that most of the databases are sitting on shard0001. Indeed, mongostat shows that most of the reads are hitting that one machine.

I have (so far) done nothing to try to influence which db goes on which machine.

My question is this: Left to it's own devices, will mongodb automatically move the primary for these small databases so that the load (eventually) ends up being more balanced, or do I have to intervene in the process myself?

(Or should I try to partition these databases over multiple machines so that the index size is smaller on each shard, then re-sequence my reads so that I hit each database one at a time?)


texts: How can I populate a table with a foreign key?

texts:
For my GCSE I need to Create and populate a third table called entries using query scripts. This table should contain foreign keys to allow sensible links to be made with the other two tables, together with the dates of each exam.

Whenever I try to insert records into my entries table it says I am not able to due to the foreign key.

Any help would be great thanks


texts: In SQL Server what is the database cost for setting a default value on table that already has over 1mil records?

texts:
NOTE: I am not a dba. :)

I have a database table that a client needs to set a column's default value. This table has 1.5mil+ records in it and this column is already populated on all existing records (they do this manually right now). They would like this column to be set automatically on all new records that their users create.

Would it cause down time or any major latency issues to set this column's default value during business hours?


texts: Are CREATE INDEX statements replicated in row-based Mysql Replication?

texts: I've not been able to find a simple explanation of what kinds of statements are or are not replicated from a master db to a slave in MySQL, row-based replication.  Specifically I want to add a fulltext index to a table and am wondering if i'll have to do that on each slave too or if that will get replicated.


texts: I am very much aware that native SQL server backups has full/differential/log backup strategies which are not really an incremental backups and would like to know if there are any 3rd party tools to implement an incremental backup strategy ?


texts: Postgres optimizing join of 2 large tables

texts:
I have 2 tables one with around 1M entries and other around 4M. I want to join these tables in a materialized view. But the materialized view creation is taking too long ~4 hours.
I don't see the indexes being used, which is fine as per this answer. When a join involves the whole tables then indices don't help so much.
So what is the workaround? My tables have time-series data. Would TimeScaleDB extension be of any help? Any other suggestion to improve the performance ?
EDIT
CREATE TABLE public.fco_assets
(
  id serial,
  symbol character varying(64) NOT NULL,
  currency integer NOT NULL,
  CONSTRAINT fco_assets_pkey1 PRIMARY KEY (id),
  CONSTRAINT fco_assets_symbol_key1 UNIQUE (symbol)
)

CREATE TABLE public.fco_prices
(
  aid integer NOT NULL,
  cid integer NOT NULL,
  "time" timestamp without time zone NOT NULL,
  close double precision,
  CONSTRAINT fco_prices_pkey PRIMARY KEY (aid, cid, "time")
)

CREATE TABLE public.fco_chr_res
(
  pid integer NOT NULL,
  aid integer NOT NULL,
  cid integer NOT NULL,
  "time" timestamp without time zone NOT NULL,
  res double precision[] NOT NULL,
  CONSTRAINT fco_chr_res_pkey PRIMARY KEY (pid, aid, cid, "time")
)

There are around 8M rows in fco_chr_res. I have created various indices on price and res tables besides the primary key which should act as an index.
 select  count(*)
 FROM fco_prices pr
 join fco_assets a on pr.aid = a.id and pr.cid=a.currency
 LEFT JOIN fco_chr_res ch ON pr.aid = ch.aid AND pr.cid = ch.cid AND pr."time" = ch."time"

For above query here is the Explain/Analysis report

Aggregate (rows=1 loops=1)  1   1
Gather (rows=1 loops=1) 1   1
Aggregate (rows=1 loops=1)  1   1
Merge Left Join (rows=45636664 loops=1) 45636664    1
Merge Inner Join (rows=21023282 loops=1)    21023282    1
Index Only Scan using fco_prices_pkey on fco_prices as pr (rows=21023282 loops=1)   21023282    1
Sort (rows=3428 loops=1)    3428    1
Seq Scan on fco_assets as a (rows=3428 loops=1) 3428    1
Index Only Scan using aid_cid_time on fco_chr_res as ch (rows=34245723 loops=1) 34245723    1

is there any scope for improvement ?


texts: Clustered Index vs Primary Key Performance

texts:
I'm trying to tune a stored procedure which has a temp table used for matching (only retrieval, no updates or inserts). We're considering applying a clustered key to a temp table. We're also considering applying a primary key (clustered).

Aside from the unique/non-null constraint of a primary key, are there any differences performance wise between clustered indexes and primary keys in regards to looking up values in the temp table?

Thanks.


texts: Switching from a SQL Server 2016 Failover Cluster to Availability Groups

texts:
The DBA role fell into my lap and now I was asked to investigate Always On Availability Groups.

So, in the Availability Group environment, do all servers need to have the same specifications?
Say I have Server A with a 20 core enterprise environment, do servers B & C have to have 20 Core each?
How does Disaster Recovery work?
Currently I have a mirror to an offsite with a decent connection, do I have to up the connection bandwidth? Is the DR site part of the Availability Group?
I was under the impression (probably 100% wrong), that if the db is in recovery mode, not accessible, you don’t have to pay for the SQL Server licenses. Now with Always On sounds like you are forced to get the licenses even if you are not accessing the server.
Using Availability Groups, can I create a primary server for one group and set a different primary server for another AG, is that possible?


texts: MySQL views performance for alias purposes

texts:
So I'm working on a Rails project and I have a legacy database, that is used by other applications, hence its structure cannot be modified.
The problem is that table and column names do not follow the Rails conventions. A common solution to that is to create MySQL views that just alias columns properly.

These are dead simple views that just have

SELECT old_column_1 AS new_column_1, old_column_2 AS new_column_2 FROM table_name;

Some tables are really big, like 22 million rows (2GiB). All the tables are MYISAM. Also there's one MEMORY table with 2 million rows (~350MiB)

The key question is how much of an overhead do I get with such views and does it in any way impact ROW LOCK or TABLE LOCK during INSERTs or JOINs?


texts: Can I import database to mysql 5.7 from msyql 8.0.19?

texts:
I used to use mysql 5.7 for a WordPress site, then I transferred the site to another server using mysql 8.0.19, but I've been problems, so I planning to create another server with mysql 5.7, but since the database was imported to a higher version, I don't know if do a mysqldump and bring the database to the server running 5.7.

Does it matter the msyql lower version to import database?
There is any special preparation to import database to older version?
Can i just mysqldump the database and import it to the 5.7 version?


texts: Postgres 10 - Query slows down with order by

texts:
I am running a query like

select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d';

on table that looks like

                Table "public.students"
          Column       |            Type             |             Modifiers
    -------------------+-----------------------------+------------------------------------
     id                | uuid                        | not null default gen_random_uuid()
     school_id        | uuid                        |
Indexes:
    "students_pkey" PRIMARY KEY, btree (id)
    "students_school_id_idx" btree (school_id)

The query plan for the select statement with just where looks like below-

explain select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d';
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on students  (cost=581.83..83357.10 rows=24954 width=16)
   Recheck Cond: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)
   ->  Bitmap Index Scan on students_school_id_idx  (cost=0.00..575.59 rows=24954 width=0)
         Index Cond: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)

This is fairly fast.

Now we add order by to the query with id that degrades the query.(Such a query is generated by Rails like student.first with some condition)

explain select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d' order by id asc limit 1;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..488.51 rows=1 width=16)
   ->  Index Scan using students_pkey on students  (cost=0.43..12179370.22 rows=24954 width=16)
         Filter: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)

How can I improve the speed to return the results of this query? Currently there are around 4990731 records in the table and is taking more than 2 minutes!
Its running on RDS with db.t2.medium instance.

UPDATE
After running Analyze students;

explain select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d' order by id asc limit 1;
                                                       QUERY PLAN
    -----------------------------------------------------------------------------------------------------------------
     Limit  (cost=8.46..8.46 rows=1 width=16)
       ->  Sort  (cost=8.46..8.46 rows=1 width=16)
             Sort Key: id
             ->  Index Scan using students_school_id_idx on students  (cost=0.43..8.45 rows=1 width=16)
                   Index Cond: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)

    explain analyze select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d' order by id asc limit 1;
                                                                          QUERY PLAN
    -----------------------------------------------------------------------------------------------------------------------------------------------------------
    Limit  (cost=8.46..8.46 rows=1 width=16) (actual time=1.853..1.855 rows=1 loops=1)
     ->  Sort  (cost=8.46..8.46 rows=1 width=16) (actual time=1.851..1.852 rows=1 loops=1)
           Sort Key: id
           Sort Method: quicksort  Memory: 25kB
           ->  Index Scan using students_school_id_idx on students  (cost=0.43..8.45 rows=1 width=16) (actual time=1.841..1.843 rows=1 loops=1)
                 Index Cond: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)
    Planning time: 0.145 ms
    Execution time: 1.874 ms


texts: SQL Server link to MySQL reports "using password: no"

texts:
I would like to access a MySQL database from within SQL Server.  I created a system DSN, and it tests OK.  I created a linked server in SQL Server:

Provider = Microsoft OLE DB Provider for ODBC Drivers
Product name = MySQL
Data source = (the name of the DSN)
Security = one entry for each of my local logins, with my MySQL user name and password

However, if I try to test it I get this error message:

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked
server "X".
OLE DB provider "MSDASQL" for linked server "X" returned message "[MySQL][ODBC
5.3(w) Driver]Access denied for user 'me'@'localhost' (using password: NO)".
(Microsoft SQL Server, Error: 7303)

The password is most definitely set, and works just fine in MySQL Workbench.  It's an admin on MySQL.  Both machines are local.  I tried adding my creds as the default ("for a login not in the list, use this security context"); no change.

I set up my DSN as 64-bit, matching my OS, using "MySQL ODBC 5.3 Unicode Driver", which I gather was installed with the MySQL engine and/or MySQL Workbench.  The 32-bit ODBC app offers the usual Portuguese drivers (why Portuguese?!) - Access, dBase, Excel, Paradox, etc.


texts:
I'm trying to implement a methodology to check the integrity of a restored database. It goes as following:

Create a snapshot of the database.
Gather various metadata:

On disk used space.
Name of tables.
Number of rows in each tables.
...

Generate a cryptographic hash using the data in each table.

My issue goes with the 3rd step to generate the cryptographic hash. I plan to read each row one by one and update my hash with the data they contain. But for my check to match I have to make sure that the row order stays the same.

How can I make sure to always get the same row order, assuming that I don't know upfront the table structure and on what column I can order on.


texts:
I have a table  adjacency, which stores information about connected lines and their angle to each other is build by

CREATE TABLE public.adjacency
(
    currentedge integer,
    currentgeom geometry(LineString,25832),
    sourcenode integer,
    targetnode integer,
    nextedge integer,
    nextgeom geometry(LineString,25832),
    degrees double precision
)

Now, I want to select only Lines which have angles in specific intervals

DROP TABLE IF EXISTS restriction;
SELECT DISTINCT '100' as to_cost, a.nextedge as target_id, a.currentedge as via_path
INTO restriction
FROM adjacency a ,  adjacency b
WHERE (a.sourcenode = b.sourcenode AND (a.degrees < 45.0 OR a.degrees > 315.0))
    OR (a.targetnode = b.targetnode AND (a.degrees < 45.0 OR a.degrees > 315.0))
    OR (a.targetnode = b.sourcenode AND (a.degrees BETWEEN 46.0 AND 224.0))
AND a.nextedge=b.currentedge

The execution of the last command seems endless. Can anyone explain to me what to change here? My execution plan:

Seq Scan on adjacency a  (cost=0.00..2574.30 rows=30630 width=40)


texts: Using exception with raise_application_error

texts:
I'm developing with Oracle 11g. How can I use a named exception in RAISE_APPLICATION_ERROR like this:

DECLARE
  my_fault EXCEPTION;
  PRAGMA EXCEPTION_INIT (my_fault, -20000);
BEGIN
  RAISE_APPLICATION_ERROR(my_fault, 'my error message');
END;

But I got an error. I have to use the error code explicity.
The Oracle Database Documentation says:

  Restriction on exception

  You can use exception only in an EXCEPTION_INIT pragma, RAISE statement, RAISE_APPLICATION_ERROR invocation, or exception handler.

I'm confused.


texts:
I have two tables
table1:

|  id |  A   | B   | C    |    date1     |    date2     |
|-----|------|-----|------|--------------|--------------|
|  1  | 100  | 100 |  100 | 10-Jan-2019  |  11-Jan-2019 |
|  2  | 100  | 100 |  100 | 10-Jan-2019  |  12-Jan-2019 |
|  3  | 200  | 200 |  100 | 10-Jan-2019  |  13-Jan-2019 |

table2:

|  id |  A   | B   | C    |        date1         | date2 |
|-----|------|-----|------|----------------------|-------|
|  x  | 100  | 100 |  100 | 10-Jan-2019 08:00:00 |       |
|  y  | 100  | 100 |  100 | 10-Jan-2019 07:00:00 |       |
|  z  | 200  | 200 |  100 | 10-Jan-2019 03:00:00 |       |

I want to update the date2 column in the table2 by matching on column A,B,C of table1 and table2.

I have this query

with matched_id (id) as (
update table2 t2
set t2.date2 = t1.date1
from table1 t1
where t1.a=t2.a AND t1.b= t2.b
and t1.c = t2.c
and tochar(t1.date1, 'dd-mm-yyyy') = tochar(t2.date1, 'dd-mm-yyyy')
and t2.date2 is null
Returning t1.id
)
Delete table1 where id in (select id from matched_id);

Right now the date2 filed is getting updated with 11-Jan for both id x,y in table 2. I want to update the records to date2 as 11-Jan and 12-Jan.
In other words if one record from table2 is matched with a record in table 1, it should not match with other records in table1.

Is there a way to achieve this in a single query?


texts:
I have a question about the installation of SQL Server 2016.

We Installed a trial version tha was made on 2017/09/19

According to the script that I execute in the database, the expiration date was 2018/03/18 but we can still use it without problems.

The Sharepoint site that uses this DB does not give any errors or warnings legends that it is expired or that some features are no longer available. I can also enter from MSQLServer Management Studio and execute scripts, create new DB, etc.

How can exactly know the expiration date?

SELECT
@@SERVERNAME as SERVERNAME,
CREATE_DATE as INSTALALTIONDATE,
SERVERPROPERTY('EDITION') as Version,
DATEADD(DD, 180, CREATE_DATE) AS "EXPIRY DATE"
FROM SYS.SERVER_PRINCIPALS
WHERE SID = 0X010100000000000512000000

SERVERNAME      INSTALLATIONDATE        Version                                 EXPIRY DATE
SAHARCVSPS02    2017-09-19 13:15:20.967 Enterprise Evaluation Edition (64-bit)  2018-03-18 13:15:20.967


texts: What is the purpose of a buffer and a buffer manager?

texts:
In a database system, what is the purpose of a buffer and a buffer manager?

This concept currently came up in my class and am very interested to learn more.


texts: Is it possible to resume an interupted mysql import from a backup file?

texts:
I created a backup file of my database with phpMyAdmin and checked the following data dump option:

include column names in every INSERT statement
      Example: INSERT INTO tbl_name (col_A,col_B,col_C) VALUES (1,2,3)

For now I get an error because some tables rows at the beginning of the import already exist, results of the incomplete import.

Is it possible to resume the import of this file, which has already been started but not finished ? I would like to do it without deleting the database content, nor modifying too much of the backup file, is it possible ?


texts: Which stored procedures are using a specifc index

texts:
I've been reviewing the indexes in our databases (adding missing indexes and removing bad ones). I stumbled on an index I created months ago which looks way to good to be true. Since the last server reboot (about 2 weeks ago) there had been only 109 writes to it, but nearly 2.7 million reads from it. Normally when I see more reads than writes I put it down as a good job and move on to the next, but this one just looks too good to be true.

So my question: It there a way to determine which store procedures are using the index? Other than reading through all stored procedures manually and checking joins and where conditions.


texts:
I have a brand new server with SQL Server 2016.

It has 24 CPU, about 80go of RAM.

The thing is, sometimes, the CPU gets pretty high (> 70%) with no specific reason at all.

If I look at the 'execute sp_WhoIsActive @get_locks=1' I get about 40 queries running, but without lock, and some of them for more than 30 seconds instead of a few ms.

This append about once a day in production. The only fix I've got so far is changing the option "cost threshold for parallelism" from 90 to 89 and from 89 to 90. Changing this value actually fix the issue in less than 10 seconds and I get a CPU charge getting lower and my user happier.

Do you guys have any idea about the root of this issue? I've thought about plan cache but I don't know what to do with that idea...

Edit : I've disable Intel HyperThreading but the issue is still the same. I've put a task cleaning the cache every hour. Do you have any idea ?

Edit 2 : I have open an issu on Microsoft Support. Apparently there is an issue with page rentention solved with a reboot of SQL Server 2016. I'll let you know ASAP if I have a "real" fixe for this.


texts: How to make large "in" queries in C# for an indexed field in MSSQL?

texts:
In mssql, how can I run effectively this query:
select id from table where field in (gigantic list of ordered longs)

Table schema:
table:
{
  id primary key
  field long
} index findex (field)

I think this should be really fast (since it's basically just stepping through two ordered lists of longs).  Is there a way to continuously stream the IDs in?
I can do it from a console or using Linqpad/SSMS.
Current (slow) Solution: a console app which constructs many long queries, each with 3k fieldId arguments.  The limit on argument number is the problem; how to get around it?


texts: MySQL Cluster MaxNoOfConcurrentOperations and RAM

texts:
Why increasing of MaxNoOfConcurrentOperations lead to higher consumption of RAM?

I need high value for fast filling tables with some random data, but i noticed that my datanodes are swapping at starting with high values (5,000,000 - 10,000,000).

There is another way to solve it: use innodb then alter table, but there might be other difficulties (some people complain about "lock wait timeout exceeded").

I just want to know how it working and how number of operations tied with RAM.


texts: Generate unique random string to be used as value for a field inside newly created rows in PostgreSQL

texts:
I have this statement which generate a random string:

SELECT string_agg (substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ceil (random() * 62)::integer, 1), '')
FROM generate_series(1, 20);

My question, how to implement this inside a function which would assign the value of the above statement automatically to a field named url_prefix when new records are created (on INSERT's) ?


texts: Config settings in MySQL so that it saves data on disk monthwise/yearwise

texts:
I have billions of rows in my MySQL table. So when I do a select for a specific date range , it takes minutes to return results, or sometimes even hangs up.

Is there any config settings in MySQL so that it saves data on disk monthwise/yearwise?

I know that MySQL saves table data in disk in table.myd file. I am looking for some config changes in MySQL by which we have multiple myd files for the same table for each month/year.


texts:
I keep getting confused trying to normalize this data.

A company makes and sells a product online. There are ten types of Products, each having a code and price.

Their Sales Order department takes customer information (name, address, trimmer product code and purchase quantity) from a simple email posting.

The products are shipped to the customer and the shipments logged into a delivery book, giving customer name and address, shipper (Parcel force, TNT, ANC, Overnite, Royal Mail), date shipped, quantity and product code.

Products are sold on a 30 day return policy, so after one calendar month of shipping an invoice is sent to the customer. The invoice details each product line supplied, unit cost of each line and sub-total, as well as the shipment date and the overall invoice cost (grand total).

Customer payments (cheque numbers) are logged and matched against a copy of the invoice before banking. Every month the log is searched for outstanding debts and these are compiled into an outstanding invoice report.

So I've created some data to help me understand:

This data has helped me alot when it comes to understanding what I need to do in terms of my final database, but I'm finding normalizing it tricky as I've got so much information. So I came up with this:

I would appreciate it if someone could give me some feedback and advice as this attempt feels wrong.


texts: Always On - Restore a node from a Veeam backup

texts:
I have a cluster with 2 nodes, and Veeam is backing up databases every day and logs every 5 minutes.  This works great and I can easily recover a single database to any point in time.

Then I was wondering what will happen if I completely lose a node. Let's say, worst case scenario, OS is gone. The AG will handle the failover and databases will still be accessible.

How can I restore the lost node?

I went into testing. To avoid a full reinstall of the node I took a backup of the VM. But obviously databases contained in the backup are older than those on the primary node of the cluster. So when I tried this I end up being stuck with databases out of sync.

First thing I did after the backup was restored was to restart the machine to kill. Still out of sync. As far as I understand, the backup I used was not a SQL database backup, and databases were in use (not that good) and the second error the databases on the primary and the broken secondary took different paths.

I found a really inefficient way to do it: remove databases from the AG, delete them on the secondary, then re-add them to the AG and let the secondary node sync.

Is it possible to resync databases more efficiently? Particularly, without losing the primary (meaning no restoration on the primary)?


texts:
Apolgies in advance if this has been discussed before, or is even fundamental enough for it to be outlined on, say, Wikipedia. I just felt that asking would be more efficient!

Anyway, I want to ask about transactions, from a theoretical point of view. In particular, say we have two (or more) users looking at a dataset and the following occur at the times indicated (tX):

t0   User1 starts a transaction
t1   User2 starts a transaction
t2   User1 runs a DML operation on the dataset
t3   User2 runs a different DML on the same dataset
t4   User1's operation finishes and is committed
t5   User2 commits

My question is: how do the DML operations run between User1 and User2 interact with each other? Does the operation started at t2 by User1 operate on the data as it was defined at the start of their transaction (i.e., at t0), and likewise for User2's operation, so that there is no interaction? Or, if say User1 committed their operation after User2's transaction had started but before they had started their operation, would the data User2 was looking at be updated respectively? Moreover, how is consistency maintained in the case of any conflicts?


texts:
I'm on SQL Server 2012 and I have tables for different companies each one with its own customers:

CompanyA_Customers:

ID | Name
---------
11 | Foo
12 | Bar

CompanyB_Customers:

ID | Name
---------
11 | Baz
12 | Qux

And I need a single table to query them, so I did this:

CREATE VIEW Customers AS
    SELECT 'A' AS Company, ID, Name FROM CompanyA_Customers
UNION ALL
    SELECT 'B' AS Company, ID, Name FROM CompanyB_Customers

but I suffer poor performance since as far as I know on a UNION view I can't define any index.

Are there any alternatives to have better performance?


texts:
I attached the DB schema, so it will be simple.

Problem: Need to get the item records from the company e.g. 'A'.

Should I add to the item table company_id field?
Will it be a redundancy info as we can always take it using user_company ?

Thank you in advance.


texts: Parse a unix/epoch value as a `TIMESTAMP` in PostgreSQL

texts:
PostgreSQL's COPY command is useful to quickly import lots of data, and the data must be in the textual representation for the datatype.

I am importing lots of data, includes a timestamp column, but it's stored as "unix time", i.e. seconds since the epoch. I can convert it to ISO 8601 (e.g. 2010-01-01 00:00:00, and PostgreSQL accept that for a timestamp. It does not accept the raw epoch value integer.

Is it possible to get postgres to accept the epoch integer value and interpret/convert that to a timestamp? That would make my code simpiler (and maybe faster).

This works with psql:

create temporary table test1 ( v1 timestamp );
copy test1 from stdin ;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF     signal.
>> 2010-01-01 00:00:00
>> \.
COPY 1

But these don't:

copy test1 from stdin ;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 946684800
>> \.
ERROR:  date/time field value out of range: "946684800"
HINT:  Perhaps you need a different "datestyle" setting.
CONTEXT:  COPY test1, line 1, column v1: "946684800"
copy test1 from stdin ;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> epoch + 946684800
>> \.
ERROR:  time zone displacement out of range: "epoch + 946684800"
CONTEXT:  COPY test1, line 1, column v1: "epoch + 946684800"

The to_timestamp function is able to accept an 'integer' or the string text of that integer. I can't see an option like this for DateStyle setting. I have tried with the column being a timestamp with time zone type, with the same results.

I'm currently runing PostgreSQL 10 on Ubuntu Linux, but upgrading to 11 (or 12 soon) is possible if needed.


texts:
Attempting to install Drupal 7.16 (also attempted 7.12) onto my local machine, I get the following error at the database stage.

  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

Looking at the Drupal database with PHPMyAdmin, I see the "variables" table is created, together two columns; that is all that gets created prior to the error.

I can't find a single solution to this.


texts: Duplicate records returned from table with no duplicates

texts:
I have a stored procedure that queries a busy queue table that is used to distribute work in our system. The table in question has a primary key on WorkID and no duplicates.

A simplified version of the query is:

INSERT INTO #TempWorkIDs (WorkID)
SELECT
        W.WorkID

    FROM
        dbo.WorkTable W

    WHERE
        (@bool_param = 0 AND
        ((W.InProgress = 0
         AND ISNULL(W.UserID, -1) != @userid_param
         AND (@bool_filtered = 0
              OR W.TypeID IN (SELECT TypeID FROM #Types AS t)))
         OR
         (@bool_param = 1
          AND W.InProgress = 1
          AND W.UserID != @userid_param)
        OR
        (@Auto_Param = 0
         AND W.UserID = @userid_param)))
         OR
         (@bool_param = 1 AND W.UserID = @userid_param)
    OPTION
        (RECOMPILE)

The #Types table is populated earlier in the procedure.

As I said, WorkTable is busy, and sometimes while this query is running I SUSPECT one of the records is moving from one set of filters in the WHERE to another. Specifically, this happens when someone starts to work on an item, and the W.InProgress changes from 0 to 1. When this happens I get a duplicate key violation when I try to add a primary key to the temp table this query is inserting into.

I have confirmed in the query plan generated when the error occurs that there is no parallelism, the isolation level is READ COMMITTED, and there are no duplicate records in the source table. You can also see there are no JOINs or other way to get cartesian products in here.

This is the anonymized query plan:

The question is, what is causing the duplicates and how can I get it to stop?

I think READ COMMITTED should work here, I need locking. I am almost positive the dupes occur when the InProgress bit on a record changes while I'm querying. I know this because the table stores the time of that change and it's within milliseconds of when I query and get the error.


texts: Randomly generated password for mysql admin over time?

texts:
I have this idea of making my database's Admin account secured by creating the following:

Trigger is activated every hour in mysql this trigger activates a stored procedure
Stored procedure generates a new password using md5(date()+pre shared key+rand())
I have a application in vb that generates the same password every hour that I can then log into with.

However this is still just an idea and I don't know if this is possible, do you know if anyone has tried this?

Any links or reference material.

I was inspired by my friend who has a key that is generated in a little device thing that he hangs around his neck when he logs into a workstation, does anyone know what the this security procedure is called?

What are the limitations or problems that could occur? (server crash/restart)

Thanks!


texts:
I have two tables one is Category and other one is Product.

Table Description are

Category

name
description
parent_id (self referencing key) (can be upto 3 levels)

Product

name
description
type
category_id (foreign key to Category table)

I want to show all the categories on a single along with the associated products. So for getting associated categories I use the below query. I am able to do the left join at the self referential tables but I am not able to get list of product data, as this is a subquery and subquery would only return a single column.

    select
               cat1.id, ARRAY(select name, type, description from product where
               product.category_id = cat1.id)
               as category_1_products_data,

               cat2.id, ARRAY(select name, type, description from product where
               product.category_id = cat2.id)
               as category_2_products_data,

               cat3.id, ARRAY(select name, type, description from product where
               product.category_id = cat3.id)
               as category_3_products_data
    from       category cat1
    left join  category cat2
    on         cat2.parent_id = cat1.id
    left join  category cat3
    on         cat3.parent_id = cat2.id
    where      cat1.parent_id is null;

  ERROR:  subquery must return only one column
   LINE 2:                        cat1.id, (select name, type, description from ...


texts:
For production backup, MongoDB recommends mongodump instead of mongoexport for accuracy of data. However, I would need to "scrub" data off a MongoDB database before backing it up. I am not aware of any server-side data scrubbing options other than mongoexport. Two questions:

Does mongoexport access MongoDB cache in RAM? Would it alter the working set in RAM like mongodump does?
mongodump command has this --query option. "Provides a JSON document as a query that optionally limits the documents included in the output of mongodump." Does it take a query to exclude certain fields in a document?


texts: SQL Server tuning: Disk queue to OS drive: How to optimize?

texts:
I expect to see the disk queue move on the data, tempdb, log and backup volumes.

I do not expect to see the OS drive (C:) which has zero SQL files present, show disk queue impact.

Assume it is to the OS paging file, or ?

Suggestions on how to chase?

This is SQL 2014 on Win2012-R2/


texts:
I have two systems with Windows XP. I have installed SQL Server 2005 on a system and tried to use SQL Server Management Studio Express on both to use the database.

I am able to use the database on the system on which I have installed it, but on the other system I get the error:

  remote database access problem

I am using Windows authentication mode on SQL Server 2005.

Is it possible to connect the database remotely or not? If yes, then how I can do it? Any other ideas to resolve this issue?


texts:
Is there a way how to 'inject' a cardinality estimation to a SQL Server optimizer (any version)?

i.e. something similar to Oracle's cardinality hint.

My motivation is driven by the article, How Good Are Query Optimizers, Really?[1], where they test the influence of the cardinality estimator on a selection of a bad plan. Therefore, it would be sufficient if I could force the SQL Server to 'estimate' the cardinalities precisely for complex queries.

[1] Leis, Viktor, et al. "How good are query optimizers, really?"
 Proceedings of the VLDB Endowment 9.3 (2015): 204-215.


texts: Can Mysql Cluster AQL(Push-Down Join) be trigger with sharding table at the same time?

texts: From the jonas slide, I knew that the feature of "Push-Down Join" can make join 70x faster. the most important factor is to eliminate the round trip between API node and data nodes. where all data in the same node, it is not necessary to do inter-node communication. But what if a certain table is partitioned to some group of data nodes, how AQL eliminate the data shipping? can push-down work in sharding scene?


texts:
I need to create a new table by combining the contents of two different tables. Is there any way to transform columns into rows and rows into columns in MySQL?

I tried using JOIN but I can't seem to transform a column into a row. Please refer to the image below for the task I need to do:


texts: sp_BlitzIndex only results in one row (with priority -1)

texts:
Not sure why, but when I run sp_BlitzIndex (v4.5), it always results in only one row (even if set @GetAllDatabases = 1).  This is the "header" row that shows Priority = -1.  What am I doing wrong?  I don't see any errors in the results or message tabs.  I'm using SQL 12.0.5000.0

Below is a copy of the messages:

Starting run. sp_BlitzIndex(TM) v4.5 - November 15, 2016
Create temp tables.

CCLocalDemo
Inserting data into #IndexColumns for clustered indexes and heaps
Inserting data into #IndexColumns for nonclustered indexes
Inserting data into #IndexSanity
Updating #IndexSanity.key_column_names
Updating #IndexSanity.partition_key_column_name
Updating #IndexSanity.key_column_names_with_sort_order
Updating #IndexSanity.key_column_names_with_sort_order_no_types (for create tsql)
Updating #IndexSanity.include_column_names
Updating #IndexSanity.include_column_names_no_types (for create tsql)
Updating #IndexSanity.count_key_columns and count_include_columns
Preferring non-2012 syntax with LEFT JOIN to sys.dm_db_index_operational_stats
Inserting data into #IndexPartitionSanity
Updating index_sanity_id on #IndexPartitionSanity
Inserting data into #IndexSanitySize
Adding UQ index on #IndexSanity (database_id, object_id, index_id)
Inserting data into #MissingIndexes
Inserting data into #ForeignKeys
Updating #IndexSanity.referenced_by_foreign_key
Update index_secret on #IndexSanity for NC indexes.
Update index_secret on #IndexSanity for heaps and non-unique clustered.
Populate #IndexCreateTsql.
Populate #PartitionCompressionInfo.
Update #PartitionCompressionInfo.
Gathering Computed Column Info.
@Mode=0 or 4, we are diagnosing.
check_id 1: Duplicate keys
check_id 2: Keys w/ identical leading columns.
check_id 11: Total lock wait time > 5 minutes (row + page) with long average waits
check_id 12: Total lock wait time > 5 minutes (row + page) with short average waits
check_id 20: >=7 NC indexes on any given table. Yes, 7 is an arbitrary number.
check_id 21: >=5 percent of indexes are unused. Yes, 5 is an arbitrary number.
check_id 22: NC indexes with 0 reads. (Borderline)
check_id 23: Indexes with 7 or more columns. (Borderline)
check_id 24: Wide clustered indexes (> 3 columns or > 16 bytes).
check_id 25: Addicted to nullable columns.
check_id 26: Wide tables (35+ cols or > 2000 non-LOB bytes).
check_id 27: Addicted to strings.
check_id 28: Non-unique clustered index.
check_id 30: No indexes with includes
check_id 31: < 3 percent of indexes have includes
check_id 32: filtered indexes and indexed views
check_id 33: Potential filtered indexes based on column names.
check_id 40: Fillfactor in nonclustered 80 percent or less
check_id 40: Fillfactor in clustered 80 percent or less
check_id 41: Hypothetical indexes
check_id 42: Disabled indexes
check_id 43: Heaps with forwarded records or deletes
check_id 44: Large Heaps with reads or writes.
check_id 45: Medium Heaps with reads or writes.
check_id 46: Small Heaps with reads or writes.
check_id 47: Heap with a Nonclustered Primary Key
check_id 50: Indexaphobia.
check_id 60: XML indexes
check_id 61: Columnstore indexes
check_id 62: Spatial indexes
check_id 63: Compressed indexes
check_id 64: Partitioned
check_id 65: Non-Aligned Partitioned
check_id 66: Recently created tables/indexes (1 week)
check_id 67: Recently modified tables/indexes (2 days)
check_id 68: Identity columns within 30 percent of the end of range
check_id 69: Column collation does not match database collation
check_id 70: Replicated columns
check_id 71: Cascading updates or cascading deletes.
check_id 80: Most scanned indexes (index_usage_stats)
check_id 81: Top recent accesses (op stats)
check_id 90: Outdated statistics
check_id 91: Statistics with a low sample rate
check_id 92: Statistics with NO RECOMPUTE
check_id 93: Statistics with filters
check_id 99: Computed Columns That Reference Functions
check_id 100: Computed Columns that are not Persisted.
Insert a row to help people find help
Returning results.


texts: How can the concatenatation of a text and a NULL value return a non-null result?

texts:
Assert 1
The concatenation operator || can concatenate any string type values, returning text. In Postgres, every type has a text representation and can be cast to text. Consequently, quoting the manual:

However, the string concatenation operator (||) still accepts non-string input, so long as at least one input is of a string type

Related:

How to concatenate columns in a Postgres SELECT?

Assert 2
Concatenating one or more NULL values makes the result NULL.
test=# SELECT (text 'foo' || NULL) IS NULL
test-#      , (text 'bar' || char '1' || NULL ) IS NULL
test-#      , (NULL::bigint || text 'baz') IS NULL;
 ?column? | ?column? | ?column?
----------+----------+----------
 t        | t        | t

Question
Is it possible to concatenate a text and a NULL value and get a non-null result?
In other words, how is this possible?
test=# SELECT col IS NULL AS col_is_null
test-#     , (text 'foo' || col) IS NULL AS result_is_null
test-# FROM   tbl;
 col_is_null | result_is_null
-------------+----------------
 t           | f

Applies to any Postgres version.
A client of mine stumbled over this, relying on the result to be NULL, and I found it intriguing enough to share.
It's a bit of a trick question as I know the answer.
Note: CASE or COALESCE catching NULL values are typically good style, but that's not what this question is about. It's about concatenation with an actual NULL value, with the concatenation operator || and still getting a non-null result.


texts:
assume we have 1 Billion records and want to store them in One or Three tables by post_type.

post_id   -> primary key
post_type -> ( page, group, user )
user_id   -> index
date      -> index

Note: we have enough RAM to store all data in buffer pool. ( about 40 GB )
In our situation, Reading from database is too much

the question is: WHILE the whole data is ON buffer pool ( RAM ), is there any difference in performance to have 3 separated tables and Join them, or have 1 big table and Select from it without any Join.

In some cases it's better for us to have separated tables for other kind of light queries.
But we fear of JOINS.