texts: Calculate Num_Index_Pages for sizing design

texts:
I don't understand Step2 #8 Calculate the number of number of non-leaf pages in the index.  I am not sure I understand the "Level".

I have Num_Leaf_Pages=508 & Index_Rows_Per_Page=476

I don't understand how they arrived at
Num_Index_Pages = 1000/(253)+ 1000/(252) + 1000/(251) = 1 + 2 + 40 = 43
"which is the number of pages described in the example."

1000/253 does not give 1, and 1000/252 does not give 2...What am I missing with regard to the "level" and how can I plug my values of 508 and 476 in and be confident with the result.


texts: MyISAM : Should I be concerned by these warnings?

texts:
I restored some tables from recent Databases backups. Because Im supporting a legacy system that only uses MyISAM tables in their DBs, for backing up I used a little script that pretty much copies all the database fils (the folders with the *.frm *.MYD and *.MYI).

At first everything seemed ok, I could see and query every table without problem, but just to be sure I did a little check in MySQL Administrator and got this:

Status checked for table fotdb.tabfotlog.
1 client is using or hasn't closed the table properly
------------------------------------------------------------
Status checked for table fotdb.tabfotlog.
Size of indexfile is: 11223040        Should be: 11224064
------------------------------------------------------------
Status checked for table fotdb.tabfotlog.
Size of datafile is: 28854024         Should be: 28854668
------------------------------------------------------------
Status checked for table fotdb.tabfotlog.
Corrupt

After that the table was labeled as corrupt and I couldn't access to it, so I did a little repair and everything went back to normal.

My guts tell me it has to do with the fact that the tables were used and written in while copying (because availability needs I cannot lock the tables while I copy them, and since every table by design is completely independent one from another, it is acceptable to have different point of time for each table).

My question is, should I be particularly worried about this? If it was this easy to fix I think I just can get by with this, but there may be bigger concerns Im not aware of and that's what I want to know. Specially since MyISAM documentation is particularly difficult to find.


texts:
--Setup, actual queries after the space
--create table fltrind (a integer,b integer)
truncate table fltrind
DROP INDEX fltrind.nf
DROP INDEX fltrind.f
DECLARE @i integer
SET @i=0
while @i<100
BEGIN
INSERT INTO fltrind(a) values(@i)
SET @i=@i+1
END
INSERT INTO fltrind(a,b) values(9000,0900)

insert into fltrind(a,b)
select top 100000 f1.a,f1.b from fltrind f1 , fltrind f2

create nonclustered index nf on fltrind(b) INCLUDE(a)
create nonclustered index f on fltrind(b) INCLUDE(a) where b is not null

UPDATE STATISTICS fltrind WITH FULLSCAN

select a from fltrind where b is not null

DROP INDEX fltrind.nf

UPDATE STATISTICS fltrind WITH FULLSCAN

select a from fltrind where b is not null

Looking at the query plan, the filtered index is not used as long as the non-filtered index is present.  Any idea why, and how can I get it to use the filtered index?

Dropping the non filtered index nf makes the optimizer use the filtered index f.

In fact, increasing the rowcount so that >10% of the rows qualify results in a table scan when the non filtered index is dropped.


texts:
I have this situation where i have two different entities, Researcher and Lab Manager and i want for a both to have a profile.
When a user logs in he could be one of the two entities, and searching in both tables for a username can be inefficient, so i think both of theme sharing a table called profile would be the way to go.
here is my attempt

but i realized two issues with it

instead of a researcher or manager have a profile, a profile have to have both a researcher and a manager as owners in the same time
when i query for a user how can i know his type without adding an attribute of type

any ideas would be appreciated


texts:
I have a huge sparse matrix ( 10^9 rows, 10^6 cols, density < 0.03% ), where each row has at least one nonzero column, but some column may contain only zeros. The cells are decimal numbers > 0.

I am looking for some db (preferably key-value) that can retrieve whole row or whole column as fast as possible. Also, I don't need to do any analytics in the DB.

I've come across SciDB which should be fast with multidimensional data, but I am afraid it is too complex for my needs.

Also, another option is to use SQL db ( probably Postgres ) but that is a bit slow and can't be scaled as easily as most NoSQL ( I expect fast row increase in matrix ).

So my biggest hope is some key-value storage, but I am not sure how to represent the matrix.

Using CRS format - but I have no idea how to implement it using key-value store.
Maybe something like { key, key, value } and have indexed both keys, but I am not sure if this is even possible. - So far I've found some clues about secondary index, but I can't imagine how would it look like.

I have some experience with SQL databases but almost none with NoSQL :(


texts: Maintaining Referential Integrity with a Filestream enabled database

texts:
We have a production database which is filestream enabled and has grown, within a year, to about 20Gb as it stores documents. On many occasions we need to bring the live data back to UAT or Dev environments for development or testing purposes.

This takes a long time and in all cases we don't need the documents so our plan was to split the database into two databases, one containing all the 'normal' tables and the other the filestream data. (Partial backup doesn't seem to be an option as restoring only the non-filestream data doesn't work).

To ensure referential integrity between the two databases, the intention was to use triggers on the 'parent' table and triggers on the 'child' table (in the filestream database) but triggers do not seem to be supported with filestream tables (When I try and do an insert I get the error:

The target table <Table> of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

Also, even if managed to ensure that no 'child' record could be inserted without a corresponding 'parent' record, my tests, so far, have shown that I could potentially insert a parent record and if the child insert fails, then I'm left with a parent record containing all the metadata but no filestream content for that record.

What are current best practices for ensuring RI between two databases, where one is filestream enable or is there a better approach to our issue?


texts:
I was asked to look into performance of a query in SQL Server 2016 SP2, and I found something that I have not seen before:

<Warnings>
<SpillToTempDb SpillLevel="0" SpilledThreadCount="1" />
<ExchangeSpillDetails WritesToTempDb="237" />
</Warnings>

Does anyone have any information on "Spill level 0"?

Before everyone sends me off to lmgtfy.com -  I've been there :-) and there is literally just a single result for this on Google; and on that page "Spill Level 0" is mentioned, but no other info.

I've looked in my SQL Server internals books, Bing.com, and so on.
Nothing.

My guess is that it has something to do with control thread spilling over, or perhaps intra-query deadlock?
The query itself is pretty basic; SELECT DISTINCT with 3 INNER JOINs, followed by 2 LEFT.

Any clue will be greatly appreciated.

And please note: The query performance has been fixed, but the mystery of that spill remains. I am not asking for help to improve this query - that's why it is not included here. I simply want to get an idea about spill 0.

Environment: SQL Server 2016 Ent. SP2 (no CUs) . MAXDOP = 4 set by RG., SSMS v18.5

I also include here a screenshot of these two operators in the plan.

Thank you!


texts: Azure server names vs Azure database names

texts: Let's say I have one Azure subscription and 2 Azure instances:  Dev-instance.database.windows.net for developer testing, and Prod-instance.windows.net for production work.  I want to create a new database named 'Employee'on both instances.  Can I name the database the same on both instances, or do the database names have to be unique since I only have one subscription?


texts: How can i tune below proc either by changing query or adding index

texts:
I am having issues with one of the proc running very slow approx an hour:
Please pardon as I have to write entire code out of system to paste here. I will be editng the question with more details on the table schema and indexes as i get to copy that info:
SP is something like below
CREATE PROCEDURE [dbo].[testsp1]
@parameter1 VARCHAR(MAX),
@parameter2 DATETIME

AS

DECLARE @Today DATETIME =GETDATE()
IF @parameter2 IS NULL
 SET @parameter2 = Getdate()

SET @parameter2 = CONVERT(VARCHAR, @parameter2 , 101)

DECLARE @table1 TABLE ( tabletypeID BIGINT)

Insert into @table1
SELECT Value from stringsplit(@parameter1, '|')

UPDATE bigtable
    SET value1 = 33, statusdate = @today
from
    bigtable t
    INNER JOIN anotherbigtable t1 on t.id=t1.id
    INNER JOIN @table1 t3 on t.tabletypeid = t3.tabletypeid
WHERE
    t1.paydate <= @parameter2
    AND t.value1=32
    AND t1.payid =2

bigtable  is approx 800 M rows and has clustered as PK on id and NC on userid( 1st key), tabletypeid (2nd key)
anotherbigtable  is approx. 300 M rows
@parameter2 is most of times received as NULL while @parameter1 varies
cached plan looks something like below


texts: SQL Server Replication - Unable to start agent job - Username or Password Incorrect

texts:
I have recently tried to setup replication. We are using the following topology:

Publisher Server -> Distributor Server -> Subscription Server

We currently have this setup using a different set of servers and it works without issue and i have used this set of servers as my template, so to speak.

With my new set of servers, i have been receiving the following SQL Agent error message when trying to run my initial snapshot from Publisher to Distributor:

Unable to start execution of step 2 (reason: Error authenticating proxy MyDomain\MyUserAccount, system error: The user name or password is incorrect.)

I have confirmed that i have the valid credentials regarding my user account and that it has sufficient access to the SQL instances in question. Additionally, it can also write to the snapshot folder share.

I have even ripped everything out and started a fresh, with a new user account.

I have tried to setup my publication using the existing distributor server we have and it went straight through without issue.

I have Googled this error message a lot, and nothing seems to have worked or be relevant to my issue at hand.

Any pointers would be greatly appreciated.


texts: sp_testlinkedserver taking long time to time-out

texts:
I'm using the following code to check if a linked server is available before running queries on it:

declare @srvr nvarchar(128), @retval int;
set @srvr = 'my_linked_srvr';
begin try
    exec @retval = sys.sp_testlinkedserver @srvr;
end try
begin catch
    set @retval = sign(@@error);
end catch;
if @retval <> 0
  raiserror('Unable to connect to server. This operation will be tried later!', 16, 2 );

I've set the 'Connection Timeout' and 'Query Timeout' to 3 seconds on the linked server settings.  If I stop the SQL Server that is linked, the script takes 3 seconds to time out and report the failure.

If I pull the network cables or disable the network adapters, it takes 18 seconds to timeout.

Why is it not adhering to the timeout settings and can I speed this up?  18 seconds to each time to test 3 linked servers is a little too slow.


texts: Query takes forever with a NOT EXISTS condition

texts:
I have two queries, one which has a condition that uc_id and ce_id are in tableUCP , and another which only requires rows which doesn't exist in the tableUCP table.

tableA : ~600k rows
tableB : ~600k rows (exactly equal to TableA)
tableCEM: ~380k rows
tableCE: ~500k rows
tableUCP: ~200k rows
tableND: ~600k rows (exactly equal to TableA)

1st query: (Runs in ~ 20 seconds) (INSERTs 0 rows)

select count(*)
FROM tableA
    JOIN tableB
        ON tableA.user_id = tableB.user_id
        AND tableA.module = tableB.module
    LEFT JOIN (select DISTINCT c_id,module_id from tableCEM where current = TRUE) cem
        ON tableB.module_id = cem.module_id
    LEFT JOIN (select * from tableCE where type = 'module' AND current = TRUE) ce
        ON tableA.module = ce.module
    JOIN (select * from tableUC where current = TRUE) uc
        ON cem.c_id = uc.c_id
        AND tableB.user_id = uc.user_id
    JOIN tableND
        ON uc.uc_id= tableND.uc_id
        AND ce.ce_id = tableND.ce_id
    JOIN (SELECT * FROM tableUCP WHERE current = TRUE) ucp
        ON uc.uc_id = ucp.uc_id
        AND ce.ce_id = ucp.ce_id;

2nd query: (Keep running for hours) (Should INSERT ~600k rows)

select count(*)
FROM tableA
    JOIN tableB
        ON tableA.user_id = tableB.user_id
        AND tableA.module = tableB.module
    LEFT JOIN (select DISTINCT c_id,module_id from tableCEM where current = TRUE) cem
        ON tableB.module_id = cem.module_id
    LEFT JOIN (select * from tableCE where type = 'module' AND current = TRUE) ce
        ON tableA.module = ce.module
    JOIN (select * from tableUC where current = TRUE) uc
        ON cem.c_id = uc.c_id
        AND tableB.user_id = uc.user_id
    JOIN tableND
        ON uc.uc_id = tableND.uc_id
        AND ce.ce_id = tableND.ce_id
    LEFT JOIN (SELECT * FROM tableUCP WHERE current = TRUE) ucp
        ON uc.uc_id = ucp.uc_id
        AND ce.ce_id = ucp.ce_id
    WHERE ucp.uc_id is NULL OR ucp.ce_id is NULL;

The only difference between the two is the last JOIN condition.
Why am I getting so different results for the two queries ?

Query Plans for both the queries

I'm sorry I wouldn't be able to share any sample data, but any help or suggestions would be tremendously helpful.

I have also tried the following:

Using NOT EXISTS for both columns individually.
Using NOT EXISTS for both columns together.
Using IN (tableA EXCEPT tableB).

All three keep running without an end.


texts: SQL Server 2005, Foreign key check against part of a table

texts:
I have an articles table that references a categories table.  I'm defining the foreign key like this:

constraint fk_categoryid foreign key (categoryid) references categories (categoryid)
on update no action
on delete no action

Is it possible in the foreign key definition to restrict the categories that can be referenced based on another column in the categories table?  For example, say there is a hasarticles column in the categories table.  I'd like the foreign key constraint to be restricted to those categories where hasarticles = true.


texts: Which IN-LIST values are not in the table

texts:
I'm looking for a simple way of seeing which items in my IN-LIST are not actually in the table. It seemed really simple but I think I'm probably over-thinking it.

I have a long list of item codes supplied by a customer and they would like an attribute in the table updating for each of those item codes.
However, I need to let the customer know if any of the codes he has provided don't actually exist in the table.
I've tried doing a COUNT(*) and a GROUP BY and I've tried some variations using MINUS but it always comes down to the fact that if the value isn't in the IN-LIST I can't refer to it.

Example:
Item table has 3 entries

Item_code
12345
98765
65436

User supplies list of

12345
98765
65436
81764

I want to highlight to the customer that the code '81764' is not a valid item code.
I can do this easily with PL/SQL but I would like a SQL solution if possible.


texts:
First things first: I'm using MS SQL Server 2008 with a database at compatibility level 80, and connecting to it with .Net's System.Data.SqlClient.SqlConnection.

For performance reasons I've created an indexed view. As a result, updates to tables referenced in the view need to be done with ARITHABORT ON. However, the profiler shows that SqlClient is connecting with ARITHABORT OFF, so updates to those tables are failing.

Is there a central configuration setting to make SqlClient use ARITHABORT ON? The best I've been able to find is to manually execute that each time a connection is opened, but updating the existing code base to do this would be a fairly large task so I'm keen to find a better way.


texts: H2 database binary file full of @^@^@^@^

texts:
I am investigating H2 database performance and was curious to why the database file was several megabytes long when only storing hundreds kilos of text, I therefore opened the database file with vim and checked what was inside this binary file.

Unsurprisingly it stored data in a somewhat readable way but what really makes me curious is why between each database row it needs to insert between 30 and 80 rows of:

@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@ [...]

I don't know what character it is and its meaning but above all why they need so much seemingly useless repeated data?

Therefore I know why database files are so big but not why they need to be so big and full of "@^" characters.

EDIT:
Ok found out those are null values <00><00><00><00><00><00>, so definitely they are useless there.
Only answer I gave myself is that H2 someway keeps some free space by putting those zeroes waiting to be filled with data, still would like some answer and how to set how much space to reserve.


texts: I'm attempting to write a stored procedure that can read from a file and act on the contents

texts:
I have a text file filled with SELECT statements that I would like to reference from a stored procedure. This is what my text file looks like:

SELECT '4200100116' AS [SRC_GEOID]
       ,SUM([Workers_in_Flow])
FROM [table]
WHERE [DEST_GEOID] IN ('4200100116'
                      ,'4200105880'
                      ,'4200132144'
                      ,'4213357872')

I want to iterate through the text file and run these commands and append the results to a table. Is this possible? If so, how?

I've looked into T-SQL and gotten conflicting ideas on whether that's what I want or not. Also, I've learned about BULK INSERT but that seems to be more about inserting into a table whereas I want to run the actual commands and append the results.


texts: Does SSMS follow the same support lifecycle as SQL Server?

texts: I've found information about the support lifecycle for SQL Server (such as this page for SQL Server 2008/2008R2), but haven't found any information about SSMS. Does SSMS follow the same support lifecycle?


texts: Figuring out if SQL Server database needs to be defragmented

texts:
I am tasked with looking at if a database in SQL Server 2014 needs to be defragmented.  I ran DBCC SHOWCONTIG but am having trouble interpreting the results.

use databasename;
GO
DBCC SHOWCONTIG ('Table');
GO

The results are:

DBCC SHOWCONTIG scanning 'Table' table...
Table: 'Table' (802206008); index ID: 1, database ID: 20
TABLE level scan performed.
- Pages Scanned................................: 2239
- Extents Scanned..............................: 280
- Extent Switches..............................: 279
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [280:280]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 31.79%
- Avg. Bytes Free per Page.....................: 366.8
- Avg. Page Density (full).....................: 95.47%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Questions:

What do the percentages above mean? (like the difference between logical and extent scan fragmentation).
What percentage do I need to check and what value indicates the disk needs to be defragmented?


texts:
I have a very large table: Table1 in a production environment which I need to alter a column in.

What's the best preferred way to do so without down time?

Option 1:

CREATE TABLE Table1_new LIKE Table1;
INSERT INTO Table1_new SELECT * FROM Table1;
ALERT TABLE Table1_new ADD COLUMN a;

ALERT TABLE Table1 RENAME Table1_bu;
ALERT TABLE Table1_new RENAME Table1;

###And then
INSERT IGNORE INTO Table1 SELECT ... FROM Table1 WHERE id > ..;

MariaDB: 5.6.44

InnoDB: 5.6.44


texts: Are SQL Server 2000 trace files generated in a .tdf format, the same way as SQL Server 2008 R2?


texts: postgresql - a local replication entry doesn't work

texts:
Maybe it's just too early in the morning, but I can't seem to get pg_basebackup working for a local user. This is part of testing for an hourly backup automated script. I added a line to pg_hba.conf for this and it looks like:

local     replication      postgres                      trust

But, this entry causes a FATAL error: no pg_hba.conf entry for replication connection from host "[local]", user "postgres", SSL off. What did I screw up? I'll be changing the user once I get this part working. The pg_basebackup command looks like:

pg_basebackup -D - -Ft -z -c fast -l hourly.backup > backup_file.gz

FYI, if I add "-h ", it does work as I do have another pg_hba.conf entry for:

host    replication   postgres   10.10.1.129       md5

Does local not work for replication entries?

(Using PostgreSQL 9.3)


texts:
I have a table of zip codes with latitudes / longitudes.  Unfortunately it doesn't seem to have a few zip codes.   I would like to try to approximate the latitude / longitude based on other zip codes that are at least in the area.

So for zip code 52733 which is not in my database,  I was thinking of taking the average latitudes of all other zip codes that are "LIKE 5273%".

 select ZipCode as z,
      (select AVG(Latitude)
       from tlkp_ZipCodes
       where ZipCode like LEFT(z, 4) + '%' )
 from tlkp_ZipCodes

That doesn't seem to work though.  It says "z" is an invalid column name.  How do I do this?


texts: Selecting, inserting, updating and deleting in a single query - possibly?

texts:
I am new to SQL Server and have only very basic knowledge in SQL. Is it possible to create a single SQL query out of the following:

Select all rows where movedTo is not empty
Copy all these entries one by one into another table, but copying only a few columns as the other table does not have the same structure (let's assume that serial and reason are the same and movedTo should be inserted into destination
Now overwrite the region column in the prior selected results with the content of the each movedTo column row by row
Now empty the movedTo and e.g. movedDate column of all at the beginning selected rows.

I know how to do it in NoSQL, as I would just use for-loops, variables, JSON arrays and stuff, but I am lost realising it in SQL.

Hope someone can help me out on this.


texts: Do non-relevant columns affect query time of select statements?

texts:
I'm just curious.

Say you have a table of 1 million records/ rows.

select order_value from store.orders

Does it make a difference whether that table has 1 field, 2 fields, or 100 fields, in actual query time? I mean all fields other than "order_value."

Right now I'm pushing data to a data warehouse. Sometimes I dump fields into the table that "may be used in the future, someday" - but they aren't being queried right now, by anything. Would these 'extraneous' fields affect select statements that do no include them, directly or indirectly (no * I mean)?


texts: Checking if schema name is included in every sentence

texts:
I got some oracle database scripts for creating the whole database of an application, including sentences for creating tables, sequences, synonyms, inserts and updates of data, etc

I need to ensure that all these sentences have the schema name before table name, e.g.

INSERT INTO SCHEMANAME.MYTABLE...

I need to check this for each sentence, regardless of whether they are creates, inserts, foreign keys sentences, etc.

And here is the question, ¿is there a way to do this kind of check? maybe through sqldeveloper tool setting a specific configuration?

Maybe the right question here could be also, if is there a way to execute the script and show an error when the executed sentence has not the schema name included?

Thanks in advance.


texts:
I am working with SqlServer and Java.

I have the following (pseudo) code :

begin transaction
  For each item of a list
    select the row in db
    ...
    update the row
  End for
end transaction

Is it possible to have a deadlock in this situation if two transactions are running with the same items ?

My understanding is the following :
For each item the select take a Shared lock on the row, and next the lock is upgraded to a Exclusive lock for the update. The lock are released at the end of the transaction. Is it true ? If yes I think that could explain a deadlock, if the first transaction tries to read a row locked by the second transaction that is trying to read a row already locked too, isn't it ?

Thks


texts: How to check growth of database in mysql?

texts:
I want to know is there any method to check the growth of database on file
EXAMPLES

Database A contains all tables in INNODB storage engine
Database B contains all tables in MyISAM storage engine
Database C contains a mixture of InnoDB and MyISAM tables


texts: Getting Machine name from where stored procedure was last executed

texts:
Is there any way to find out machine IP or machine name from where a particular stored procedure is executed in SQL Server 2005?

We have a client server architecture with .NET as a front end and SQL Server 2005 as back end. Someone in production environment has executed one stored procedure & deleted critical data from .NET application. We are unable to deduce who has done this.

We know the exact time when this has happened. Is there any way we can find the all the connection in SQL Server at that time, so that we can find out machine name from logged connection?


texts: Set statistics time showing different elapsed_time than dm_exec_query_stats view

texts:
When I exec a SQL statement after setting set statistics time on and running dbcc freeproccache and dbcc dropcleanbuffers I get the following output:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 17 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
SQL Server parse and compile time:
   CPU time = 796 ms, elapsed time = 891 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(0 row(s) affected)

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 9 ms.

SQL Server Execution Times:
   CPU time = 796 ms,  elapsed time = 900 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

SQL Server Execution Times:
   CPU time = 796 ms,  elapsed time = 917 ms.

I then query the the sys.dm_exec_query_stats table using the following query:

select
    OBJECT_NAME(q.objectid,q.dbid) AS ProcedureName,
    highest_cpu_queries.creation_time,
    highest_cpu_queries.total_worker_time,
    highest_cpu_queries.total_elapsed_time,
    highest_cpu_queries.last_worker_time,
    BlockTime = highest_cpu_queries.total_elapsed_time - highest_cpu_queries.total_worker_time,
    highest_cpu_queries.execution_count,
    highest_cpu_queries.total_worker_time / highest_cpu_queries.execution_count AS avg_time,
    highest_cpu_queries.min_worker_time,
    highest_cpu_queries.max_worker_time,
    highest_cpu_queries.max_elapsed_time,
    DB_NAME(q.dbid),
    q.dbid,
    q.objectid,
    q.number,
    q.encrypted,
    q.[text]
from
    (select top 50
        qs.plan_handle,
        qs.creation_time,
        qs.total_worker_time,
        qs.total_elapsed_time,
        qs.last_worker_time,
        qs.min_worker_time,
        qs.max_worker_time,
        qs.max_elapsed_time,
        qs.execution_count
    from
        sys.dm_exec_query_stats qs
    order by qs.total_worker_time desc) as highest_cpu_queries
    cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc

This returns 1 row as expected (since the dbcc commands reset the view). The total_elapsed_time column shows 8830 (946 total_worker_time + 7884 blocktime).

Total_elapsed_time according to msdn is in microseconds but only accurate to milliseconds.

So this should be 8-9ms. Set Statistics time above is showing 917ms.

My understanding was the dm_exec_query_stats view captures the same numbers as Set Statistics Time. Why are the elapsed times reported different?


texts: Why does adding an index increase the execution time in SQLite?

texts:
I'll just show you an example. Here candidates is a table of 1000000 candidates from 1000 teams and their individual scores. We want a list of all teams and whether the total score of all candidates within each team is within the top 50.
(Note that all CREATE TABLE results AS ... statements are identical, and the only difference is the presence of indices. These tables are created (and dropped) to suppress the query results so that they won't make a lot of noise in the output.)
------------
-- Set up --
------------

.open delete-me.db    -- A persistent database file is required

.print ''
.print '[Set up]'

DROP TABLE IF EXISTS candidates;

CREATE TABLE candidates AS
WITH RECURSIVE candidates(team, score) AS (
    SELECT ABS(RANDOM()) % 1000, 1
    UNION
    SELECT ABS(RANDOM()) % 1000, score + 1
    FROM candidates
    LIMIT 1000000
)
SELECT team, score
FROM candidates;

-------------------
-- Without Index --
-------------------

.print ''
.print '[Without Index]'

DROP TABLE IF EXISTS results;

ANALYZE;

.timer ON
.eqp   ON
CREATE TABLE results AS
WITH top_teams_verbose(top_team, total_score) AS (
    SELECT team, SUM(score)
    FROM candidates
    GROUP BY team
    ORDER BY 2 DESC
    LIMIT 50
),
top_teams AS (
    SELECT top_team
    FROM top_teams_verbose
)
SELECT team, SUM(team IN top_teams)
FROM candidates
GROUP BY team;
.eqp   OFF
.timer OFF

------------------------------
-- With Single-column Index --
------------------------------

.print ''
.print '[With Single-column Index]'

CREATE INDEX candidates_idx_1 ON candidates(team);

DROP TABLE IF EXISTS results;

ANALYZE;

.timer ON
.eqp   ON
CREATE TABLE results AS
WITH top_teams_verbose(top_team, total_score) AS (
    SELECT team, SUM(score)
    FROM candidates
    GROUP BY team
    ORDER BY 2 DESC
    LIMIT 50
),
top_teams AS (
    SELECT top_team
    FROM top_teams_verbose
)
SELECT team, SUM(team IN top_teams)
FROM candidates
GROUP BY team;
.eqp   OFF
.timer OFF

-----------------------------
-- With Multi-column Index --
-----------------------------

.print ''
.print '[With Multi-column Index]'

CREATE INDEX candidates_idx_2 ON candidates(team, score);

DROP TABLE IF EXISTS results;

ANALYZE;

.timer ON
.eqp   ON
CREATE TABLE results AS
WITH top_teams_verbose(top_team, total_score) AS (
    SELECT team, SUM(score)
    FROM candidates
    GROUP BY team
    ORDER BY 2 DESC
    LIMIT 50
),
top_teams AS (
    SELECT top_team
    FROM top_teams_verbose
)
SELECT team, SUM(team IN top_teams)
FROM candidates
GROUP BY team;
.eqp   OFF
.timer OFF

Here is the output
[Set up]

[Without Index]
QUERY PLAN
|--SCAN TABLE candidates
|--USE TEMP B-TREE FOR GROUP BY
`--LIST SUBQUERY 3
   |--CO-ROUTINE 1
   |  |--SCAN TABLE candidates
   |  |--USE TEMP B-TREE FOR GROUP BY
   |  `--USE TEMP B-TREE FOR ORDER BY
   `--SCAN SUBQUERY 1
Run Time: real 0.958 user 0.923953 sys 0.030911

[With Single-column Index]
QUERY PLAN
|--SCAN TABLE candidates USING COVERING INDEX candidates_idx_1
`--LIST SUBQUERY 3
   |--CO-ROUTINE 1
   |  |--SCAN TABLE candidates USING INDEX candidates_idx_1
   |  `--USE TEMP B-TREE FOR ORDER BY
   `--SCAN SUBQUERY 1
Run Time: real 2.487 user 1.108399 sys 1.375656

[With Multi-column Index]
QUERY PLAN
|--SCAN TABLE candidates USING COVERING INDEX candidates_idx_1
`--LIST SUBQUERY 3
   |--CO-ROUTINE 1
   |  |--SCAN TABLE candidates USING COVERING INDEX candidates_idx_2
   |  `--USE TEMP B-TREE FOR ORDER BY
   `--SCAN SUBQUERY 1
Run Time: real 0.270 user 0.248629 sys 0.014341

While the covering index candidates_idx_2 does help, it seems that the single-column index candidates_idx_1 makes the query significantly slower, even after I ran ANALYZE;. It's only 2.5x slower in this case, but I think the factor can be made greater if you fine-tune the number of candidates and teams.
Why is it?


texts:
From what I've read, typically for indexing the recommendation is to leave the fill factor at 70-80, and the logic behind this seems that new data may be inserted into a table between existing values thus fill factor shouldn't be set as 100.  In a hypothetical example of usernames, new names may be added to the name field, so a fill factor of 100 could create a problem because, even though the pages are full, the new data won't be organized appropriately with the other data.

My question involves a table where data are organized by date (clustered index) and ID (non clustered index); each order has a date and an ID thus no "new" orders can occur between existing orders and no new IDs can appear between existing IDs - these happen sequentially.  For a table like this, would it be appropriate from a performance perspective to set the fill factor to 100 and reindex at 100, meaning are there performance advantages to having the pages full since no new data will appear between existing data?


texts: Is there a way to force one optimization level in SQL Server?

texts:
Based on Paul White's blog, there are three optimization phases in SQL Server, search 0 (Transaction Processing), search 1 (quick plan) and search 2 (full optimization). Each phase has entry and termination conditions.
My question is, can we force the optimizer to run at one optimization phase?


texts: MySQL 5.6 showing wrong "second_behind_master"

texts:
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.7.98
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000032
          Read_Master_Log_Pos: 277431436
               Relay_Log_File: mysqld-relay-bin.000039
                Relay_Log_Pos: 143474118
        Relay_Master_Log_File: mysql-bin.000031
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 963696099
              Relay_Log_Space: 530952469
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 98
                  Master_UUID:
             Master_Info_File: /mnt/vol1/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: updating
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

Here second_behind_master is showing 0 even currently slave is having Relay_Master_Log_File: mysql-bin.000031 and master is having Master_Log_File: mysql-bin.000032.


texts:
From all of the database related reference books that I have come across, there is a universal rule that state a database table should be at least 3NF.

From what I understand, a table is considered 2NF if it satisfies 1NF and it has single column primary key. Correct me if I am wrong.

So I couldn't understand why there is so much argument of having composite key especially for entities that relate M:N tables; eg:

A Product_Customer table, with the columns:

CustomerProductID
CustomerID
ProductID

Some say that primary key (CustomerProductID) is not needed and it should use composite key (CustomerID, ProductID) instead.

But doesn't that violate the universal rule of a table should have at least 3NF since using a composite key will not even satisfy 2NF?

Correct me if I am wrong, especially the part where I understand that if a table has composite keys, it will not be in 2NF.


texts: Can I move directly from SQL Server SP1 to SP3 or do i need to install SP2 then go to SP3. I was assigned to upgrade my servers 2014 just to make sure I don't fall into problems


texts: PAL Report - 64-bit Windows Server 2008 R2, issues

texts:
I don't know anything about SQL Server, however I found the following issues while running the PAL report. It seems that RAM is the primary issue and it has to be increased.

Kindly suggest.

Critical:

Less than 5 percent of RAM is available or less than 64 MB of RAM is available
Less than 500 MB of free disk space
(Total Latch Wait Time) / (Latch Waits/ Sec) < 10

Warning:

A ratio of more than 1 workfile created for every 20 batch requests
A ratio of more than 1 page lookup for every 100 batch requests
A ratio of more than 1 SQL Full Scan for every 1000 Index Searches
Lock Requests/sec Greater than 1000
Deprecated Usage - Near 0
Log Shrinks Near 0
The working set of all processes combined is increasing at a rate greater than 100MBs per hour
Possible Handle Leak: More than 250 handles between overall Min and overall Max and an increasing trend of more than 100 handles per hour
Less than 90% Worktables from Cache Ratio


texts:
Msg 8906, Level 16, State 1, Line 1
  Page (1:99122) in database ID 9 is allocated in the SGAM (1:3) and PFS (1:97056), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

  Msg 8906, Level 16, State 1, Line 1
  Page (1:291360) in database ID 9 is allocated in the SGAM (1:3) and PFS (1:291168), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

  CHECKDB found 2 allocation errors and 0 consistency errors not associated with any single object.

I ran a CHECKDB and received the above error. I have very little experience with SQL errors and our Database team is not an option. I have read that I will have to perform a repair and will incur data loss. Is there anyway to predict what sort of data loss and how much by the above error? Thanks in advance.


texts: How can I merge duplicate rows in a single table?

texts:
I have a table with duplicate barcodes (barcode) and carts (cart) and (Quantity_counted_IN) and (Quantity_Counted_out).

I want to merge all duplicate records that have the same barcode and cart number to show only one barcode for that cart number but sum(Quantity_counted_IN) and sum(Quantity_Counted_out) at the same time.

Example:

           cart   barcode         Quantity_Counted_In  Quantity_Counted_Out
 Row 1        1   610708542209                     -7                    20
 Row 2        1   610708542209                     -4                    16
 Row 3        2   610708542209                     -3                    17

I want it to read:

 Row 1        1   610708542209                    -11                    36
 Row 2        2   610708542209                     -3                    17

How can I do this?


texts:
How can I monitor a production database to see if there are table scans happening? And what indexes might fix them...

I'm told that we have adequate indexes but I can't see every code path, and we have too many people touching the codebase (hey, lots of developers can sometimes be a good thing) so what's the most comprehensive way to see how if we've missed indexes based on our production load?

SQL Server 2008 R2, C#/ASP.NET code, NHibernate are the key factors in use.


texts: Most efficient way to structure segmented data

texts:
I am faced with a set of data which I am currently unsure on how to store efficiently. The data in question relates to a calendar which stores information on two different categories of event which have information both common and specific to their categories. From my application, I need to be able to withdraw all events (of both cat a and b for user 1), as well as events specific to their type (e.g all of cat a for user 1 or all of cat b for user 1).

Currently, I am storing the two different categories in their own respective tables, for instance tbl_cat1_events and tbl_cat2_events. A sample is below.

tbl_cat1_events
------------------
event_id
user_id
start_time
end_time
cat1_foo1
cat1_foo2

tbl_cat2_events
------------------
event_id
user_id
start_time
end_time
cat2_foo3
cat2_foo4

This is problematic for several reasons. First of all, when submitting a new event, I have to query both tables in order to verify there isn't a conflicting event going on at the same time. Obviously this is caused by both tables containing common information. Furthermore, when retrieving events, I'll need two queries, selecting events from both tables, after which running a quick sanity check for clashes. Within my code, I'll also need different data structures for both categories.

How can I improve the structure of my database to be more centralized while requiring less queries and generally being more efficient?


texts:
I have the query below which returns the top 5 sum per brokerage

SELECT *
FROM  (SELECT b.brokerage_name AS Brokerage,
              SUM(c.cost)      AS Cost
       FROM   (tblquotesnew q
               left join tblbrokeragesnew b
                      ON q.brokerage_id = b.id)
              left join tblcommissionnew c
                     ON q.quoteid = c.quote_id
       WHERE  c.calc_date >= Trunc(SYSDATE, 'iw')
              AND c.calc_date <= Trunc(SYSDATE, 'iw') + 7 - 1 / 86400
       GROUP  BY b.brokerage_name
       HAVING SUM(c.cost) IS NOT NULL
       ORDER  BY SUM(c.cost) DESC)
WHERE  ROWNUM <= 5
ORDER  BY ROWNUM;

Is it possible, in the same query, to have a sixth row returned for Other, which would be a sum of the remaining c.cost?


texts: Storing all data in a single db versus multiple dbs in Mongodb

texts:
We have an app that is basically an ERP. Some of the resources in the ERP are satellite tracked. So the information related to the resource itself is minimal, but the satellite tracker produces data every 3 minutes. Our instinct was to store the satellite information in it's own db or even it's own cluster and store the ERP information in it's own cluster.

What do you think?


texts: Which index type is best suited for exact-match against a list of values?

texts:
I have this table:

CREATE TABLE public.foos (
    id integer NOT NULL,
    bar_string character varying
);

I want to do this query:

SELECT id from foos where bar_string in ('one', 'two', 'three')

The size of the list will be something like 5-100.

If I know I'll only ever be doing this query, what's the best index to put on bar_string? I've read that Hash indexes are better than btree for exact matches, is this the way to go?


texts: Error in the code shown below - Why is it

texts:
Why does the following code show an error

SELECT TO_DATE('Today is the Seventeenth of December, Twenty Fifteen','"Today is the" Ddspth "of" Month, Year')
from dual


texts:
I have a strange situation, seen from the log:

Process 37278 waits for ExclusiveLock on advisory lock [16421,999999,12864385,2]; blocked by process 53807.
Process 53807 waits for ExclusiveLock on advisory lock [16421,999999,12864395,2]; blocked by process 37278.
Process 37278: SELECT * FROM zel_api.insert_event ($1 ,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24)
Process 53807: SELECT * FROM zel_api.insert_event ($1 ,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24)",
"See server log for query details.",,,"SQL statement ""SELECT pg_advisory_xact_lock(999999, format('zel_event.%I', p_table_name)::regclass::oid::integer)""

This is already strange in itself, as it looks like two processes block the same advisory lock but neither can actually grab it.

The function which tries to acquire the lock is the following:

CREATE OR REPLACE FUNCTION zel_event.create_new_partition(
    p_table_name text
)
RETURNS void AS
$BODY$
DECLARE
    _schema text;
BEGIN
    IF NOT EXISTS (table from catalog)
    THEN
        PERFORM pg_advisory_xact_lock(999999, format('zel_event.%I', p_table_name)::regclass::oid::integer);

        IF NOT EXISTS (table from catalog)
        THEN
            EXECUTE format('
                CREATE TABLE %1$I.%2$I (
                    LIKE zel_event.%2$I INCLUDING ALL
                ) INHERITS (zel_event.%2$I)', _schema, p_table_name);
        END IF;
    END IF;
    RETURN;

END;
$BODY$
LANGUAGE plpgsql
SECURITY DEFINER;

To my eyes none of the 'classic' deadlock causes stands here as there is only one logic...
The idea behind it is to create the new table on demand where the demand can come at a really high frequency from several connections.  It is called when an insertion function does the insert aimed at the parent table, being diverted by a dispatcher trigger to the appropriate child table.

This is how the transaction looks like:

INSERT INTO parent (zel_api.insert_event())
        |
     trigger
        |
        +----> child exists? ---no---> CREATE TABLE child
                      |                         |
                     yes                        |
                      |                         |
                      V                         V
              INSERT INTO child (zel_event.create_new_partition())

The PostgreSQL version is 9.2

It would be very interesting to have some insights how this can happen (and mitigated, of course).


texts: Can arbiterOnly replica in MongoDB become SECONDARY and what it means?

texts:
The MongoDB documentation says that when a replica is added to the set as arbiterOnly=true it will not become Primary. But can Arbiter become a secondary and what does it when it does?

I have a replica set consisting of two data replicas and one arbiterOnly.

Some time ago my one of my data replicas which was secondary at a time crushed due to hard drive failure. After I fixed that problem and restarted secondary it went into “Recovering” state. But my arbiter is now “Secondary”. (That is shown by rs.status() command)

Furthermore, the data directory for this Arbiter shows bunch of files of total size >10GB. They indeed look to me like data files. Are they? What is going to happen to these files when Recovery completes?

If Arbiter can become truly a data node (even secondary) that opens up the whole new bag of worms: I need to have enough free disks available to Arbiter, right now I don't. Is there option to prevent  arbiterOnly from becoming secondary?


texts: Upgrade existing MongoDB 3.0.7 cluster to MongoDB 3.2.0 cluster

texts: MongoDB 3.2 uses wiredTiger as the default storage engine, I have upgraded 3.0.7 cluster to 3.2 without touching the config files, everything works fine, but I need to convert config servers to replica set, how do I preserve the current data on config servers that are running old storage engine?


texts: To normalize or not to normalize? (10 fields)

texts:
I'm helping someone to design a database and for a specific case I'm not sure.

I have 10 fields - this will never change - that will always be filled and used in every query that could be coming up. Always.

So I came up with 2 solutions, normalized and not normalized

I would have to query the first solution like this;

    select n.Id
         , Type1Selector1.Info as Type1Selector1Info
         , Type1Selector2.Info as Type1Selector2Info
         , Type1Selector3.Info as Type1Selector3Info
         , Type1Selector4.Info as Type1Selector4Info
         , Type1Selector5.Info as Type1Selector5Info
         , Type2Selector1.Info as Type2Selector1Info
         , Type2Selector2.Info as Type2Selector2Info
         , Type2Selector3.Info as Type2Selector3Info
         , Type2Selector4.Info as Type2Selector4Info
         , Type2Selector5.Info as Type2Selector5Info

    from dbo.OptionOneMainTable n
         inner join dbo.OptionOneSecondTable Type1Selector1 on
                    Type1Selector1.Id = n.Id
                and Type1Selector1.Type = 1
                and Type1Selector1.Selector = 1
         inner join dbo.OptionOneSecondTable Type1Selector2 on
                    Type1Selector2.Id = n.Id
                and Type1Selector2.Type = 1
                and Type1Selector2.Selector = 2
         inner join dbo.OptionOneSecondTable Type1Selector3 on
                    Type1Selector3.Id = n.Id
                and Type1Selector3.Type = 1
                and Type1Selector3.Selector = 3
         inner join dbo.OptionOneSecondTable Type1Selector4 on
                    Type1Selector4.Id = n.Id
                and Type1Selector4.Type = 1
                and Type1Selector4.Selector = 4
         inner join dbo.OptionOneSecondTable Type1Selector5 on
                    Type1Selector5.Id = n.Id
                and Type1Selector5.Type = 1
                and Type1Selector5.Selector = 5
         inner join dbo.OptionOneSecondTable Type2Selector1 on
                    Type2Selector1.Id = n.Id
                and Type2Selector1.Type = 2
                and Type2Selector1.Selector = 1
         inner join dbo.OptionOneSecondTable Type2Selector2 on
                    Type2Selector2.Id = n.Id
                and Type2Selector2.Type = 2
                and Type2Selector2.Selector = 2
         inner join dbo.OptionOneSecondTable Type2Selector3 on
                    Type2Selector3.Id = n.Id
                and Type2Selector3.Type = 2
                and Type2Selector3.Selector = 3
         inner join dbo.OptionOneSecondTable Type2Selector4 on
                    Type2Selector4.Id = n.Id
                and Type2Selector4.Type = 2
                and Type2Selector4.Selector = 4
         inner join dbo.OptionOneSecondTable Type2Selector5 on
                    Type2Selector5.Id = n.Id
                and Type2Selector5.Type = 2
                and Type2Selector5.Selector = 5

and obviously the second solution would be

    select Id
         , Type1Selector1Info
         , Type1Selector2Info
         , Type1Selector3Info
         , Type1Selector4Info
         , Type1Selector5Info
         , Type2Selector1Info
         , Type2Selector2Info
         , Type2Selector3Info
         , Type2Selector4Info
         , Type2Selector5Info

    from dbo.OptionTwoMainTable

I'm not alone doing this database and I would personally take the second solution.

Should I try to convince the other person to go for solution two? He wants to go for solution one.


texts: How do I write a Postgres SQL command based on metadata of the tables themselves?

texts:
I want to retrieve all the records that were modified in the past hour of running the command. It is purely a meta data command (with no regard to the content of any tabular column data). I want to identify tables and rows of those tables that had some DML modification with a specified temporal duration.

Does Postgres need special configuration to track such detailed meta data before I run a command to return the info I am looking for?

What query would I run to get the results I am looking for?


texts: Postgres how to reference FK to primary composite key?

texts:
Having a table definition like:
CREATE TABLE foo
(
    id1 Integer NOT NULL,
    id2 Integer NOT NULL
    PRIMARY KEY (id1, id2)
);

I found it impossible for linking a foreign key from another table to refer to each of the composite primary key above separately.
I get there is no unique constraint matching given keys for referenced table.
So I tried up with new table definition like:
CREATE TABLE foo
(
id1 Integer NOT NULL,
id2 Integer NOT NULL
UNIQUE (id1, id2),
PRIMARY KEY (id1, id2)
};

it didn't go well, so I ended up with- although not sure it is ok as I already have a primary composite key:
CREATE TABLE foo
(
id1 Integer NOT NULL,
id2 Integer NOT NULL
UNIQUE (id1),
UNIQUE (id2),
PRIMARY KEY (id1, id2)
};

What am I doing wrong? I want uniqueness in terms of composite primary key not on the seperated fields.


texts: How to count rows based on a column value

texts:
I am using Posgresql and I want to write a query to perform the following.
Suppose the table is like this:

id  section views
1     1      10
2     2       8
3     1      20
4     1      21
5     2      0

I want to count each row that has the same "section" value (ordered by "views") like this:

 id  section views contador
 4   1        21     1       //section 1 contador 1
 3   1        20     2       //section 1 contador 2 ...
 1   1        10     3
 2   2         8     1   //section 2 contador 1
 5   2         0     2   //section 2 contador 2


texts: Create a job from dbms_scheduler with stream redirection?

texts:
I want to create a simple job who have to execute this shell command:

echo some_text > some_file

The attributes some_text and some_file can change between two calls to run_job so I need to use set_job_argument_value procedure.

Here is my code:

exec dbms_scheduler.create_job('write_file','executable','/bin/echo',1,auto_drop=>false);
exec dbms_scheduler.set_job_argument_value('write_file',1,'text > /tmp/file');
exec dbms_scheduler.run_job('write_file');

I begin by creating the job who will execute the echo command. This job will take only argument : text > /tmp/file.
When I run my job, the procedure works fine but my file is not created. It's like the procedure runs this command: /bin/echo "text > /tmp/file".

So I tried with multiples arguments:

exec dbms_scheduler.create_job('write_file','executable','/bin/echo',3,auto_drop=>false);
exec dbms_scheduler.set_job_argument_value('write_file',1,'text');
exec dbms_scheduler.set_job_argument_value('write_file',2,'>');
exec dbms_scheduler.set_job_argument_value('write_file',3,'/tmp/file');
exec dbms_scheduler.run_job('write_file');

Same output and no file created.

So, is it possible to specify the standard input? Or to use this kind of job_action: /bin/echo $1 > $2?


texts: How can I get rid of an unhelpful parallel branch when unpivoting a single row?

texts:
Consider the following query that unpivots a few handfuls of scalar aggregates:

SELECT A, B
FROM (
    SELECT
      MAX(CASE WHEN ID = 1 THEN 1 ELSE 0 END) VAL1
    , MAX(CASE WHEN ID = 2 THEN 1 ELSE 0 END) VAL2
    , MAX(CASE WHEN ID = 3 THEN 1 ELSE 0 END) VAL3
    , MAX(CASE WHEN ID = 4 THEN 1 ELSE 0 END) VAL4
    , MAX(CASE WHEN ID = 5 THEN 1 ELSE 0 END) VAL5
    , MAX(CASE WHEN ID = 6 THEN 1 ELSE 0 END) VAL6
    , MAX(CASE WHEN ID = 7 THEN 1 ELSE 0 END) VAL7
    , MAX(CASE WHEN ID = 16 THEN 1 ELSE 0 END) VAL16
    FROM dbo.PARALLEL_ZONE_REPRO
) q
UNPIVOT(B FOR A IN (
    VAL1
    ,VAL2
    ,VAL3
    ,VAL4
    ,VAL5
    ,VAL6
    ,VAL7
    ,VAL16
)) U
OPTION (MAXDOP 4);

On SQL Server 2017, I get a plan with two parallel branches. The left parallel branch feels out of place to me. The optimizer has a guarantee that there will be only a single row output from the global scalar aggregate, yet the parent operator of it is a Distribute Streams with round robin partitioning:

When I execute the query all of the rows go to a single thread as expected. There's no performance problem with this query, but the query reserves 8 parallel threads with MAXDOP set to 4. Again, I feel that this is out of place. It's impossible for both parallel branches to execute at the same time. I want to avoid unnecessary worker thread reservation because I have TF 2467 enabled which changes the scheduling algorithm to look at the number of worker threads per scheduler.

Is it possible to rewrite the query to have exactly one parallel branch that contains the table scan and local aggregate? For example, I would be fine with the general shape below except that I want the nested loop to execute in a serial zone:

For Application Reasons™ I strongly prefer to avoid splitting this query up into parts. If desired, you can view the actual query plan here. If you'd like to play along at home, here is T-SQL to create the table used in the query:

DROP TABLE IF EXISTS dbo.PARALLEL_ZONE_REPRO;

CREATE TABLE dbo.PARALLEL_ZONE_REPRO (
    ID BIGINT,
    FILLER VARCHAR(100)
);

INSERT INTO dbo.PARALLEL_ZONE_REPRO WITH (TABLOCK)
SELECT
  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 15
, REPLICATE('Z', 100)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;


texts: Mysql thinks Master & Slave have the same server-id

texts:
I've been setting up a master->slave_a->slave_b relationship and having a little trouble. I currently have the master->slave_a replication working fine.

Each of the two slaves have exactly the same configuration, other than the hostname, server-id setting. When ever I try and start the slave on slave_b I have the following error pop up:

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

I really don't understand, because I have all of the server IDs miles apart 1->2626->2629 (m->s->s).

I'm setting them in my /etc/mysql/conf.d/replication.cnf file - and it seems to be being included, but I have no idea how I can check which server id mysql is picking up.

Thanks in advance :) Tom.


texts: Why we can FLASHBACK DROP table where it is DDL command and hence autocommited?

texts: DROP and TRUNCATE both are DDL commands and both are autocommited.Then why we can recover DROP table by using FLASHBACK(unless it is purge) and why we can not recover TRUNCATED data(don't consider backup).


texts: SQL Server passing variable to a string in a stored proc

texts:
I am trying to concatenate a custom string with a variable that is an int. Investigation is pending for  ['+ @investigationidout +']. When I hover the mouse pointer over the first + sign it says:

  Incorrect syntax near '+'

Is this possible ?

I need to see something like:

  Investigation is pending for 1234

My query:

    EXEC sp_wf_create_notification
        @processid,
        @vactivityid,
        @vstepid,
        1,
        @vidColumn,
        @vidColumnTable,
        @investigationidout,
        @owner,
        'Pending Investigation',
        'Investigation is pending for  ['+ @investigationidout +'] '
        @nextstepurl,
        @vstatus OUTPUT,
        @verror OUTPUT


texts: How many database users/admins should be created for a website?

texts: I have created a CMS website that only had one database connection for both the admin area and the user area and it only had one user("root") with no password, and now I want to upload it, but I am not sure whether I should create 2 different users(and two distinct connections) one for the admin and the other just for displaying purposes for the site visitor(making it more restrict), In order to keep it more secure. Or does it not matter how many users one creates when looking at a security point of view?


texts:
hellow

Dumped x.sql file from db and then
trying  to find way to insert data from x.sql file to existent tbl in another server.
But mysql execute all the concept of the x.sql ( that include "drop table if exists tbl and create new")

mysql --host= --user= --password= schema tbl < x.sql

there is away may be to dump tbl (with out "drop and create new" commands only insert )?


texts: Legacy data migration to Always on Availability group database

texts:
In our application there is a data conversion process that brings all the legacy data to the production database.

Since our database is on availability group and the migration might bring 100GB + worth of data, my plan was to remove all the secondary replica from the availability group and then set the primary database offline.

Restore the production database into a staging server, then complete the migration/conversion and then restore it back to the Production and syn all the secondary replicas

Please advise if there is a better way to do the migration.


texts: How to calculate shift hours in postgres?


texts: merge like rows then update fk from another table to match the new values

texts:
I have 2 tables tb1 (id, name, record_no, location,...) tb2(id, test, date,...)

Tb1 is joined to tb2 on tb1.id = tb2.id

The issue is that TB1 has duplicated entry's that have new ids. I need to merge these ids into one 1 for each unique entry then update tb2.id to match the change.

Not sure of the most effective way to do this without having to manually update each row.

Before

SELECT * FROM TB1

ID, NAME,    DOB,        RECORD_NUM
1, John Doe, 01/01/1900, 123456789
2, John Doe, 01/01/1900, 123456789
3, Jane Doe, 11/03/2016, 294018400
4, Jane Doe, 11/03/2016, 294018400
...

SELECT * FROM TB2

ID, Test,    Result, Date
1,  English, Pass,   01/01/1900
1,  Grammer, Fail,   01/02/1900
2,  Gym,     Pass,   01/01/1900
3,  Art,     Pass,   11/02/2016
4,  Gym,     Pass,   11/03/2016
...

Basically I need to take row ID 2 and merge it will ID 1 from TB1 then where 2 appears in TB2 I need to update it to 1.

I know the entries are the same usually by the Record_num or if that is a null value I can use the name and dob (since together they should be unique in the set).

After

SELECT * FROM TB1

ID, NAME,    DOB,        RECORD_NUM
1, John Doe, 01/01/1900, 123456789
3, Jane Doe, 11/03/2016, 294018400
...

SELECT * FROM TB2

ID, Test,    Result, Date
1,  English, Pass,   01/01/1900
1,  Grammer, Fail,   01/02/1900
1,  Gym,     Pass,   01/01/1900
3,  Art,     Pass,   11/02/2016
3,  Gym,     Pass,   11/03/2016
...

I hope this helps explain a little more.


texts: Find Unique Index that does not have Unique Constraint

texts:
I need to find list of unique indices that does not have unique constraint on that column. How to achieve this?
I know, following query will give me unique constraints.
SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE='UNIQUE'

Note: I am using SQL Server 2012.


texts:
I am using in a Bash Shell Script this code for retrieving a random date.

Is there a way to do that also in Microsoft SQL Server?

I can imagine that through a single SELECT query and probably by calling a few functions that can be performed easily, somehow.


texts:
I have two tables with many to many relationship, which I join on `userId and then sum/count values so I would get the summary of them. This is how it looks:
            select COUNT(DISTINCT [s].[UserId]) AS NumberOfCustomers,
            sum(s.[ProbabilityAlive]) as ProbabilityAlive,
            avg([s].[ExpectedMonetaryValue]) as ExpectedMonetaryValue, sum(ExpectedTransactions) as ExpectedTransactions,
            sum(s.[CustomerLifetimeValue]) as CustomerLifetimeValue, sum(s.[ResidualLifeTimeValue]) as ResidualLifetimeValue,
            sum(s.[Transactions]) as Transactions, sum(s.[TotalSpend]) as TotalSpend,
            sum(s.[DeltaProbabilityAlive]) as DeltaProbabilityAlive, sum(s.[ProbabilityActive]) as ProbabilityActive,
            sum(s.[DeltaProbabilityActive]) as DeltaProbabilityActive,
            sum(s.[DeltaEstimatedNumberOfTransactionsShort]) as DeltaEstimatedNumberOfTransactionsShort, sum(s.[EstimatedNumberOfTransactionsLong]) as EstimatedNumberOfTransactionsLong,
            sum(s.[DeltaEstimatedNumberOfTransactionsLong]) as DeltaEstimatedNumberOfTransactionsLong, sum(s.[EstimatedNumberOfTransactionsLongLeft]) as EstimatedNumberOfTransactionsLongLeft,
            sum(s.[ResidualLifeTimeValueLeft]) as ResidualLifeTimeValueLeft, sum(s.[CustomerLifetimeValueLeft]) as CustomerLifetimeValueLeft,
            SUM(a.PeakClv) as PeakClv
            FROM (
            SELECT DISTINCT([Clv].[UserId]), sum([dbo].[Clv].[ProbabilityAlive]) as ProbabilityAlive,
            avg([Clv].[ExpectedMonetaryValue]) as ExpectedMonetaryValue, sum([dbo].[Clv].[EstimatedNumberOfTransactionsShort]) as ExpectedTransactions,
            sum([Clv].[CustomerLifetimeValue]) as CustomerLifetimeValue, sum([dbo].[Clv].[ResidualLifeTimeValue]) as ResidualLifetimeValue,
            sum([dbo].[Clv].[NumberOfTransactions]) as Transactions, sum([dbo].[Clv].[TotalSpend]) as TotalSpend,
            sum([dbo].[Clv].[DeltaProbabilityAlive]) as DeltaProbabilityAlive, sum([dbo].[Clv].[ProbabilityActive]) as ProbabilityActive,
            sum([dbo].[Clv].[DeltaProbabilityActive]) as DeltaProbabilityActive,
            sum([dbo].[Clv].[DeltaEstimatedNumberOfTransactionsShort]) as DeltaEstimatedNumberOfTransactionsShort, sum([dbo].[Clv].[EstimatedNumberOfTransactionsLong]) as EstimatedNumberOfTransactionsLong,
            sum([dbo].[Clv].[DeltaEstimatedNumberOfTransactionsLong]) as DeltaEstimatedNumberOfTransactionsLong, sum([dbo].[Clv].[EstimatedNumberOfTransactionsLongLeft]) as EstimatedNumberOfTransactionsLongLeft,
            sum([dbo].[Clv].[ResidualLifeTimeValueLeft]) as ResidualLifeTimeValueLeft, sum([dbo].[Clv].[CustomerLifetimeValueLeft]) as CustomerLifetimeValueLeft
            FROM [Clv]  WHERE Clv.[ClientId] = '2' AND   Clv.[CalculationDate] = '02/28/2021' AND  Clv.[Product] = 'Total'  GROUP BY UserId ) s
            LEFT JOIN (
                SELECT  DISTINCT(UserId), SUM([PeakClv]) as PeakClv
                FROM [dbo].[AdditionalClvData]
                WHERE ClientId = '2' AND CalculationDate = '02/28/2021'
                GROUP BY [UserId]
            ) a ON a.[UserId] = s.[UserId]

I take almost all values from one table, and one from the other and use pre-aggregating in subqueries. Select from single table takes couple of seconds, however this query takes around 20seconds. Am I doing joining/aggregating incorrectly? Is there a way to optimise so it would work faster?


texts: sys.dm_hadr_database_replica_states, understanding the different *_time columns

texts:
I am going nuts trying to understand the meaning of the different time columns in the sys.dm_hadr_database_replica_states system table.

We have a three node Always On Availability Group, where two of the nodes are sync and one async.  We want to use the async node for reporting and want to find an accurate way to calculate the delay between the primary and async replica.

Below is a snapshot of a query:

select synchronization_state_desc,
       is_primary_replica,
       last_sent_time,
       last_received_time,
       last_hardened_time,
       last_redone_time,
       last_commit_time
from sys.dm_hadr_database_replica_states DRS
where database_id = db_id('DBname')

Edited the post to narrow down the question

If I need to find the actual delay it takes for the data to appear on async node, which columns do I use?


texts: How to fix a messed up replication on MS SQL Server

texts:
I restored a database from a backup. The database uses replication to publish to a different server. Assuming the database restore would break the replication, I tried to delete the replication and re-create it (we have a script to re-create it from scratch). I'm not sure exactly what I did, but now it is in a completely messed up state and I can't fix it.

First, I try to get rid of the subscription (on the publisher server):

EXEC sp_dropsubscription @publication = 'PublicationName', @article = N'all', @subscriber = 'SubscriberServerName'

This seems to work. SELECT * FROM syssubscriptions shows no results. Looking on the subscriber server, SSMS > {SubscriberServer} > Replication > Local Subscriptions - the subscription is not there.

So then I try to delete the publication. SSMS > {Server} > Replication > Local Publications > {PublicationName} > Delete. This gives the following error message:

Could not delete publication 'PublicationName'.
Could not drop article. A subscription exists on it.
Changed database context to 'DatabaseName'. (Microsoft SQL Server, Error: 14046)

Ok, so I try to drop the articles:

EXEC sp_droparticle @publication = 'PublicationName', @article = N'all'

and get this error:

Invalidated the existing snapshot of the publication. Run the Snapshot Agent again to generate a new snapshot.
Msg 14046, Level 16, State 1, Procedure sp_MSdrop_article, Line 75
Could not drop article. A subscription exists on it.

Ok, so I try starting the Snapshot Agent and I get this internal SQL exception:

The SQL command 'sp_MSactivate_auto_sub' had returned fewer rows than expected by the replication agent.

So I tried an alternative method of deleting the article, DELETE FROM sysarticles. This seems to have worked - I have now got rid of the articles, but I still get the same 'Cannot drop the publication because at least one subscription exists for this publication' error when I try to delete the publication.

I have also restarted SQL Server - didn't help.

I don't know what is going on here and how do I fix it?

BTW this is what happens when you give a software developer who knows just enough to be dangerous the keys to the database. Fortunately, this isn't a production environment...


texts: details on how to connect to geographically close datacenter

texts:
I am reading a book about distributed systems. One of the options of data replication mentioned is the use of a "multi leader" approach and place each leader in a different datacenter. The main point of different datacenters is to be geographically close to the user.

The author then discusses all the write conflicts that emerge by having multiple write leaders, but he doesn't say much on how to direct users to connect to geographically close data center.

For example, user in Austria makes a HTTP request to https://stackoverflow.com. Stackoverflow has datacenters in Germany and North America. DNS record point to the datacenter in US.

Is initial request always going to be pointing to the datacenter in US? I know that once a user is identified, I can instruct all AJAX and img requests to point to Germany (by changing the html response I sent back), but initial requests, such as page reload will always point to US.

This kinda defeats the purpose of being geographically close to users if they always have to connect to the distant server at first and only after that, the inline resources are fetched from a nearby server.. Am I missing some essential principles here?


texts:
Currently I am restoring SAN snapshots to another server nightly and running weekly checkdbs on that test server.  The checkdb is throwing the following errors.

  The log scan number (105217:402:0) passed to log scan in database
  'db_with_error' is not valid. This error may indicate data corruption
  or that the log file (.ldf) does not match the data file (.mdf). If
  this error occurred during replication, re-create the publication.
  Otherwise, restore from backup if the problem results in a failure
  during startup.

I have used the following to resolve the checkdb error.

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'db_with_error',
@CheckCommands = 'CHECKDB'

EXEC sp_resetstatus 'db_with_error';

ALTER DATABASE db_with_error SET EMERGENCY;

dbcc checkdb ('db_with_error')

ALTER DATABASE db_with_error SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE db_with_error SET multi_user WITH ROLLBACK IMMEDIATE;

ALTER DATABASE db_with_error SET online;
EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'db_with_error',
@CheckCommands = 'CHECKDB'

Why is the above resolving the error?

Things to note that the test server is running 12.0.2495 and the prod server is running 11.0.5548. Also the prod server is apart of an availability group which is in good standing as I understand it.


texts: Updating 3 million rows in 100million+ table

texts:
I have a really big table containing over 100kk rows and I would like to update about 3kk rows.

Following count returns 3kk and takes about 300s to execute

select count(*) from A where x=.. and y=..

But the update statement is much slower...

update A set z = .. where x=.. and y=..

V$SESSION_LONGOPS says it might take ~10-15h to finish.

What are the steps I can take to improve the performance of the above update? CTAS probably doesn't make much sense in this case (3kk rows in 100kk rows table).


texts: What are the differences between the first and subsequent rds_superusers in an AWS RDS Postgres Instance?

texts:
I have been asked to provide administration of an existing RDS Postgres instance. The AWS admin has created a new user for me and has added the rds_superuser role to it.

But this user with the rds_superuser role does not seem to have the same set of privileges as the default rds_superuser.
I cannot, for example, create new roles.
Does anyone know the difference between the default rds_superuser that comes with a new RDS PG instance and those users that are subsequently granted the rds_superuser role?


texts: postgres wal sender replication timeout during pg_basebackup

texts:
Let me start with the caveat that I am still green with Postgres.

I am working on a postgres 9.2 Active/Standby cluster on Debian wheezy for an application, based off of the ClusterLabs pgsql cluster documentation.

In the lab I am able to get this working without a problem.  But on the production cluster I'm building, I keep running into a problem.

I brought the database files over from the current single production postgres server.  By this I mean I shutdown postgres and tar-ed up the data directory and copied it over the the cluster's Master node. I put the files in place, set the permissions, and was able to start-up postgres on the Master via corosync just fine.

In preparing the slave, I used the pg_basebackup tool to bring the database over from the Master and this is where I keep having issues. As it is transferring, at about 57% I see the error:

$ pg_basebackup -h db-master -U u_repl -D /db/data/postgresql/9.2/main/ -X stream -P
pg_basebackup: could not receive data from WAL stream: SSL connection has been closed unexpectedly
176472/176472 kB (100%), 1/1 tablespace
pg_basebackup: child process exited with error 1`

And on the server, I see:

2016-04-06 21:05:31 UTC LOG:  terminating walsender process due to replication timeout

But the transfer doesn't stop and keeps going to completion.

I found this question here on stackexchange about setting "ssl_renegotiation_limit" to 0, but this didn't make much difference.

Anyone have any ideas?  I am completely baffled as to why this would error, but keep on going just fine.  It is the same procedure I used in the lab setup... the only difference is that the production database is much bigger in size.

Thoughts??
Thank you kindly!  -Peter.


texts: How prepare and transfer database from mysql 5.7 to 8.0.19 since mysql_upgrade has been removed

texts:
I noticed that the mysql_upgrade script is no longer available with mysql 8.0.16, and I'm running 8.0.19, so after creating a database backup from version 5.7 and imported to the version 8.0.19.

So, there's anything else I need to do to make sure to use the database in the new server running ubuntu 18.04?


texts:
I am very new to sql. feel a little stupid to ask this question

Say I have this table Warehouse(shipperid, name , state). Then I define another table warehouse_maine (shipperid, name, state) which should only contain the warehouse located in maine. after I insert all the data for warehouse. I want to create a trigger for warehouse_maine, which insert the warehouse data only in maine state.

CREATE TRIGGER TRIGGER01 ON WAREHOUSE_MAINE
FOR INSERT
AS select * from warehouse where warehouse.state= maine

This is what I thought but it's obviously wrong. what should be the syntax after keyword AS?


texts: PostgreSQL: pg_rewind complains about wal_log_hints = on not been set, it is

texts:
I'm rather new to pg so please bear with me.

I've set up two hosts and installed pg10 on both. Then I configured log streaming replication between them. This worked fine.

I wanted to test fail over, so I stopped postgresql service on Primary and create trigger file on Standby, this also worked find. I check and my standby is not in recovery mode anymore.

Now I want to recover old Primary as new standby. I tried using pg_rewind but it gives me the following error message

/usr/lib/postgresql/10/bin/pg_rewind --target-pgdata=/var/lib/postgresql/10/main --source-server="host=server2ip port=5432 user=postgres"

target server needs to use either data checksums or "wal_log_hints = on"
Failure, exiting

I have wal_log_hints = on set on both servers.

Can anybody help me figure out how to get this to work.

Below are my settings which are the same on both servers. Replication before failing Primary worked great.

Below are lines which I changed in postgresql .conf

listen_addresses = '*'
max_connections = 200
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 5
wal_level = replica
wal_log_hints = on
archive_mode = on
archive_command = '/bin/true'
max_wal_senders = 5
wal_keep_segments = 5000
max_replication_slots = 5
effective_cache_size = 2GB

I also added the below lines to pg_hba.conf file

host    all             postgres        server1ip/24        trust
host    all             postgres        server2ip/24        trust

And finally I create recovery.conf file as below.

standby_mode          = 'on'
primary_conninfo      = 'host=sciontsdbbc01 port=5432 user=replicate password=rep-password'
trigger_file = '/tmp/PG_MasterNow'


texts: Nested loops estimate way too low, causes tempdb spills

texts:
I'm working on tuning a query, and I think the main problem right now is that there is a Nested Loops step early on with a row estimate that is way too low, causing too little memory to be allocated and downstream steps to spill to tempdb. The troublesome step is the one in the green box below.

https://www.brentozar.com/pastetheplan/?id=Sk8E-6YAM

The two inputs into the Nested Loops both have accurate row estimates of 2.4M rows, but the output of that step is estimated at only 37 rows, while the actual is 2.4M.

All three Table Spool steps below have the exact same estimated vs actual as well, which makes me think they're getting their estimate from the Nested Loops. Every branch spills to tempdb in a Sort step.

I'm thinking if I can just get the estimated rows out of the Nested Loops corrected, it will not only prevent spills on the top branch, but that the Table Spools will also inherit the correct estimate, get a sufficient memory grant, and also not spill.

SQL Server 2016 SP2 with the legacy CE on.

Here's the query

SELECT  Object18.Column1,
        Object18.Column3,
        Function1(Object19.Column12) AS Column13,
        Function2(Object19.Column12) AS Column14,
        Function3(DISTINCT (CASE WHEN Object19.Column15 = ? THEN Object18.Column6 END)) AS Column16,
        Function3(DISTINCT (CASE WHEN Object19.Column17 = ? THEN Object18.Column6 END)) AS Column18,
        Function3(DISTINCT Object18.Column6) AS Column19
from Object8 Object18
join Object20 Object19 on Object19.Column20 = Object18.Column7 and Object19.Column3 = Object18.Column3
where Object19.Column8 = ?
GROUP BY Object18.Column1, Object18.Column3
option (recompile)


texts:
I'm trying to dump all tables of a specific MySQL database into each file(s) per table from a remote server.

I tried to use mysqldump with option --tab=dir_name as the output directory, but it seems only work locally. When I do it with option --host=remote_db_ip to connect to remote DB server, it only produces .sql files (with only table structure inside) on my server, and throws the following errors because it is using SELECT INTO OUTFILE which is trying to find that output path on the remote server machine.

mysqldump: Got error: 1: Can't create/write to file '/output/path/table_name.txt' (Errcode: 2 - No such file or directory) when executing 'SELECT INTO OUTFILE'

P.S. I know one workaround that is to dump to the remote DB server first then transfer back to my server, but the target database is huge and there is not enough extra disk space for storing dump output on the remote DB server.

MySQL version: 5.6


texts: Approach to Handling Errors/DataCorrections in Aggregated Data

texts:
We're in the process of building a vNext Data/Reporting Platform for capturing Web & Sales Traffic.

At a very high level we're capturing Impressions, Clicks & Sales Data which then needs to be reported on.

Impressions are on the order of Millions per day
Clicks: 100,000's per day
Sales: 1000's per day

My first pass design for the platform essentially aggregates data into Hourly, Daily, Weekly & Monthly structures. Each being grouped on a number of other dimensions (Client, Brand, Product, Country, Retailer etc...) with Impression Counts, Click Counts, Total Value etc...

So each hour, source data gets aggregated -> Hourly
And then each day:

the prev 24h of Hourly Data -> New Daily
the prev 24h of Hourly Data -> Added to existing Weekly or create new Weekly
the prev 24h of Hourly Data -> Added to existing Monthly or create new Monthly

This gives us a lot of flexibility for reporting efficiently as well as monitoring trends and identifying potential problems.

One thing I'm not sure on is how to "Fix" data in the event a "discrepancy" gets into our aggregated data.

e.g. Every once in a while a customer will provide incorrect pricing information. When that occurs, the "Potential Value" on each click and impression will be wrong.

So that source data ends up in the Hourly aggregates.

If unnoticed, for even one day, that hourly data pollutes the daily, weekly & monthly aggregates. And because those aggregates are being built and added to cumulatively, you can't easily undo it. (You can't pour the blue water out of the jug of purple water).

The naive solution here would seem to be to throw away the subset of aggregated data that's been polluted, fix the source data, and then regenerate each of those aggregates that's been removed. Which one day later might mean throwing away 24 Hourly's, 1 Daily, 1 Weekly & 1 Monthly.

Or is there another approach here that I'm not thinking of?


texts: How to keep history of MySQL/Aurora stored procedure revisions?

texts: We need to maintain the history of the procedures. I think it will be easier to save the history in a table so every time someone either deletes or modifies the content of the procedure it will go and save the changes to a table to maintain an history. May be upto a point a trigger can help, but wanted to check if anyone has any custom script written on this already which can be reviewed?


texts:
I'm running PostgreSQL 9.6 and would like to trial pgpool. The yum repository shows the following options:

pgpool-II-96
postgresql-pgpool-II

Which version should i use ? The documentation on PgPool references the first option, but I've found posts on here that say I should use the PostgreSQL specific repository.

Also are there any useful tutorials on getting this up and running for a first time user, i find the main documentation at pgpool.net confusing?

When I start pgpool i get the following output.

2018-01-31 17:51:13: pid 41278: DEBUG:  I am 41278
2018-01-31 17:51:13: pid 41278: DEBUG:  initializing backend status
2018-01-31 17:51:13: pid 41278: DEBUG:  authenticate kind = 0
2018-01-31 17:51:13: pid 41278: FATAL:  unable to read data from DB node 0
2018-01-31 17:51:13: pid 41278: DETAIL:  EOF encountered with backend


texts: Several PC Cannot Connect To MySQL Server on Certain Time

texts:
I have a network of 10 PCs and 1 Server. I installed MySQL Community Server v5.6.10 64-bit on the Server, and all the PCs are Windows XP, using ODBC Connection 5.2(a).

Some of the PC (previously it was 3, now become 4) cannot connect to the MySQL at a certain time, which is 2.00 pm. Before, it was OK, the client can connect to the Server normally, but at 2.00 pm, those PC cannot connect anymore, with this error:

Your connection attempt failed for user xxx@xxx from your host to server at
xxx.xxx.xxx.xxx:xxxx. Cannot connect to MySQL Server on xxx.xxx.xxx.xxx (10055)
Please:
1. Check that mysql is running on server xxx.xxx.xxx.xxx
2. Check that mysql is running on port xxxx
3. Check the xxxxxx has rights to connect to xxx.xxx.xxx.xxx from your address (mysql rights define what clients can connect to the server and from which machines)
4. Make sure you are both providing a password if needed and using the correct password for xxx.xxx.xxx.xxx connection from the host address you're connecting from

From the above error message:

I already check that mysql is running (other PC can access it normally)
I also check MySQL to run in port xxxx
I also check the user xxxxxx has rights to connect (because it can connect to the server before 2 pm)
I also check the password has been provided correctly
I also check the maximum number of connection in my.ini, which I set to 0 (unlimited connection)
I also check the network connection, but find no problem since I can still ping the server and other PC

The only action I do to make everything work again is by restarting the PC (client), however, I don't want such solution. I want to know what's the cause, is it because of the ODBC connector, the PC, or any other reason. Could anyone give me some hints on what I should check?

Thank you


texts: Optimize Subquery with Windowing Function

texts:
As my performance tuning skills never seem to feel sufficient, I always wonder if there is more optimization I can perform against some queries.  The situation that this question pertains to is a Windowed MAX function nested within a subquery.

The data that I'm digging through is a series of transactions on various groups of larger sets.  I've got 4 fields of importance, the unique ID of a transaction, the Group ID of a batch of transactions, and dates associated with the respective unique transaction or group of transactions.  Most times the Group Date matches the Maximum Unique Transaction Date for a Batch, but there are times where manual adjustments come through our system and a unique date operation occurs after the group transaction date is captured.  This manual edit doesn't adjust the group date by design.

What I identify in this query are those records where the Unique Date falls after the Group Date.  The following sample query builds out a rough equivalent of the my scenario and the SELECT statement returns the records I'm looking for, however, am I approaching this solution in the most efficient manner?  This takes a while to run during my fact table loads as my record counts number in the upper 9 digits, but mostly my disdain for subqueries makes me wonder if there's a better approach here.  I'm not as concerned about any indexes as I'm confident those are already in place; what I'm looking for is an alternative query approach that will achieve the same thing, but even more efficiently.  Any feedback is welcome.

CREATE TABLE #Example
(
    UniqueID INT IDENTITY(1,1)
  , GroupID INT
  , GroupDate DATETIME
  , UniqueDate DATETIME
)

CREATE CLUSTERED INDEX [CX_1] ON [#Example]
(
    [UniqueID] ASC
)

SET NOCOUNT ON

--Populate some test data
DECLARE @i INT = 0, @j INT = 5, @UniqueDate DATETIME, @GroupDate DATETIME

WHILE @i < 10000
BEGIN

    IF((@i + @j)%173 = 0)
    BEGIN
        SET @UniqueDate = GETDATE()+@i+5
    END
    ELSE
    BEGIN
        SET @UniqueDate = GETDATE()+@i
    END

    SET @GroupDate = GETDATE()+(@j-1)

    INSERT INTO #Example (GroupID, GroupDate, UniqueDate)
    VALUES (@j, @GroupDate, @UniqueDate)

    SET @i = @i + 1

    IF (@i % 5 = 0)
    BEGIN
        SET @j = @j+5
    END
END
SET NOCOUNT OFF

CREATE NONCLUSTERED INDEX [IX_2_4_3] ON [#Example]
(
    [GroupID] ASC,
    [UniqueDate] ASC,
    [GroupDate] ASC
)
INCLUDE ([UniqueID])

-- Identify any UniqueDates that are greater than the GroupDate within their GroupID
SELECT UniqueID
     , GroupID
     , GroupDate
     , UniqueDate
FROM (
    SELECT UniqueID
         , GroupID
         , GroupDate
         , UniqueDate
         , MAX(UniqueDate) OVER (PARTITION BY GroupID) AS maxUniqueDate
    FROM #Example
    ) calc_maxUD
WHERE maxUniqueDate > GroupDate
    AND maxUniqueDate = UniqueDate

DROP TABLE #Example

dbfiddle here


texts: Database design considerations for unused columns with every table has same schema

texts:
I am a web developer so I don't know a lot about databases. The company I joined recently has a very mature Desktop ERP built in .NET and SQL Server, they are providing services to huge corporate clients and there design is working fine. But they didn't develop any web based system. But there database design is quite unusual. Let me explain it and then I will post my questions.

So, Now I joined them to develop them a web based ERP (a replica of there desktop system in web). Since I am building the application from scratch, they have given me liberty to revamp any thing which I think would effect positively.

Now the design is,

They have around 150 tables in database.
Every table has the same schema definition.
They have divided the fields in three categories.

Strings (so they assign 50 varchar(250) fields in database).
DateTime (so they assign 15 smalldatetime fields in database).
Numeric (so they assign 30 Numeric() fields in database).

All columns have names as (these names don't terrify the developers, in a week or two they are accustomed and even remembered many of the fields associations):

Strings (S1, S2, S3, S4 and so on).
DateTime (D1, D2, D3, D4 and so on).
Numerics (N1, N2, N3, N4 and so on).

As I have told you the schema. Every table consist of 95 columns. And only 15-20 columns are actually been used. The remaining 75-80 columns are NULL.
The tables are well normalized and indexes are maintained.
Number of rows in most of the tables are less than 1000. Only the transaction table records touches several hundred thousands.
The precision for numeric columns are by default (1, 0). When any field is selected to be used then the precision is adjusted as per the requirement.
An empty database is of ~4MB.
This design makes there development quite easy. Since they have a number of columns and whenever they need a field. They just select the data type, i.e. String or Numeric or DateTime and the next available column is assigned.

I think this information is quite enough. Now I want to ask

Since, I don't know a lot about SQL. Is this design viable for web environment (web API which will be called from web client as well as from mobile)?
Since, every table has 75-80 NULL columns. Does they cost us a lot of memory in future, when transaction records will touch millions.
What are your suggestions to improve this design?

THANKS.


texts: I want to select specific columns from a table, let's say table A.  Table B has the names of the names of the columns I would like to select. How would I write a MSSQL query that will use the distinct values from Table B as the column names that I want to query from table A?


texts: Mysql get records more then 3 in interval of 1 minute, return group_concat ID

texts:
Currently i have this dataset, i need to return grouped ids that are within the range of 60 seconds and have more than 3.

CREATE TABLE test
(
  `id` bigint NOT NULL AUTO_INCREMENT,
  created_date TIMESTAMP(1) NOT NULL,
  origin_url   VARCHAR (200) NOT NULL,
  client_session_id VARCHAR (50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UC_PRE_CHAT_TALKID_COL` (`id`)
);

✓

INSERT INTO test VALUES
(1,'2021-01-18 11:02:24.0', 'https://zendes.com/', 'znkjoc3gfth2c3m0t1klii'),
(2,'2021-01-18 11:02:35.0', 'https://zendes.com/', 'znkjoc3gfth2c3m0t1klii'),
(3,'2021-01-18 11:02:03.0', 'https://zendes.com/', 'znkjoc3gfth2c3m0t1klii'),
(4,'2021-01-18 11:11:28.0', 'https://rarara.com/', 'znkjoc3gfth2c3m0t1klii'),
(5,'2021-01-18 11:11:36.0', 'https://rarara.com/', 'znkjoc3gfth2c3m0t1klii'),
(6,'2021-01-18 11:11:05.0', 'https://rarara.com/', 'znkjoc3gfth2c3m0t1klii');

✓

db<>fiddle here
something like this:
ids     origin_url              client_session_id
1,2,3   https://testett.com/    znkjoc3gfth2c3m0t1klii
4,5,6   https://rarara.com/     znkjoc3gfth2c3m0t1klii

Edit some context:
I currently developed a cron that runs every 1 minute to analyze the last 60 seconds of the bot records in database, I need to group the conversation ID's that have more than 3 records within 60 seconds in the same url and client_session_id.
follows the SQL I'm running:
select
    count(session_id),
    client_session_id,
    GROUP_CONCAT(id) as talkIds,
    origin_url
from
    bot_talk
where
    created_date > now() - interval 60 second
group by
    client_session_id, origin_url
having
    count(session_id) >= 3

This query works as I expect, but sometimes my cron service is sometimes down, and I lose those repeated records.
I thought about making an SQL(Cron) at the end of the day to analyze the last 24 hours, and look for the records that are repeated according to the rule I mentioned above?


texts: How to increase MySQL performance with joins

texts:
I have a MySQL database with InnoDB engine. Particularly one of my queries became very slow. The query is shown below:

SELECT t1.`ARTREF1`
FROM `lijnen` AS t3
  INNER JOIN
    (`kortingen` AS t2, `raiproduct` AS t1)
  ON (t1.LEV=t3.LEV
  AND t2.LEV=t3.LEV
  AND t1.KCP1=t3.SPLC
  AND t2.SPLC=t3.SPLC
  AND t1.LEV=t2.LEV
  AND t2.SPLC=t1.KCP1
  AND t1.`ARTREF1`='".$xartc."'
  AND t2.ALDOC='".$supcodec."'
  AND t3.BVNR='".$bvnr."'
    )

How it works:
I get through SOAP webservice an XML document. In the XML document I get the article number and supplier number and I query my database to see if the article exists.
It takes nearly  5 to 8 seconds to show the results on screen.

I use indexes on the rows which I search. Every day I purge empty fields to optimize my database
How can I increase the speed of the query?

Some database and hardware information:

raiproduct: 1.500.000 rows
lijnen:  4100 rows
kortingen: 1200 rows
CPU: 4 cores
Processors: Intel 64bit hexacore (6 cores) 2.5 Ghz
RAM: 4096 MB
SWAP RAM: 1024 MB

I hope someone can help me with this problem.


texts: Change pg_dump directory output to table name instead of object number

texts:
pg_dump -F d will result in multiple <number>.dat.gz files which represent the object number in the database.
I wish pg_dump create those files in the format <table name>.dat.gz instead so I'll be able to easy distinguish a file/table in a case of table recoery.


texts: Loading 260GB worth of CSV into MySQL in Linux

texts:
I'm bulk loading a few thousand CSVs totaling ~260GB and ~4.5 billion rows to MySQL using mysqlimport calls on each CSV executed within a bash script.  The MySQL installation and the data itself are on an external drive as I didn't have sufficient disk space on my machine to accommodate.  My first attempt seemed to stall after a certain juncture (~24h), so I created another table without the indexes to see if processing sped up some (figured it was slowing things down to have to reindex the table after loading each CSV).

I'm now about 36h in and a little over 50% of the way complete, but stumped as to how I could speed up processing (or if it's even possible).  Is there a better way to go about loading this much data to a MySQL table.

Edit: Processing seems to be slowing as it goes.  Roughly 50% of data loaded in the first 36h, only an additional ~10% in the 24h since.

Edit2:  I've updated innodb_buffer_pool_size to 4x its default value (128MB --> 512MB) and loading appears to be humming along--a single file was taking 15min+ to load prior to tuning, individual files are loading within ~1min now.  There's been a corresponding slight increase in RAM usage (~4.5G to ~5.6G).


texts: pg_restore using PostGIS/PostgreSQL Dump Changes Data in Geography Column

texts:
Two PostGIS databases (PostGIS 3.0, PostgreSQL 13.1) were setup on two separate machines to be as close to each other as possible using Docker images.
A dump of the database was taken from the first machine using
pg_dump --host=db1.foo.com --dbname=foo --username=postgres -Fc --file=/tmp/foo.dump

and then restored on the database on the second machine using
pg_restore --clean --dbname=foo /tmp/foo.dump

When we view a query result using a GUI software TablePlus, we noticed that the column named coordinates of type Geography contains values that look very different after restoring.
Query Result on 1st Machine (pg_dump source):
SELECT coordinates FROM locations LIMIT 5;

Query Result on 2nd Machine (after pg_restore):
SELECT coordinates FROM locations LIMIT 5;

However, our app that queries this database for coordinate data appears to be plotting the data correctly on a map. This makes me think that the data on both machines are the same, but stored in different representation.
Question: Why did the Geography data values in the column coordinates changed, and how can we restore from the dump while keeping the original data values?

Update: Tried using -b when performing pg_dump, but the problem persist.
pg_dump --host=db1.foo.com --dbname=foo --username=postgres -Fc -b --file=/tmp/foo.dump


texts: Select query takes more time than it should

texts:
I have a MySQL database table with nearly 23 million records. This table has no primary key, because nothing is unique. It has 2 columns, both are indexed. Below is its structure:

Below is some of its data:

Now, I ran a simple query:

SELECT `indexVal` FROM `key_word` WHERE `hashed_word`='001'

Unfortunately, this took more than 5 seconds to retrieve the data and show them to me. My future table will have 150 billion records, so this time is very very high.

I ran the Explain command to see what's going on. Result is below.

Then I ran the Profile using below command.

SET profiling=1;
SELECT `indexVal` FROM `key_word` WHERE `hashed_word` = '001';
SHOW profile;

Below is the result of the profiling:

Below is some more information about my table:

So, why this is taking so long? They are indexed too! In future, I have to run lot of LIKE commands, so this is taking too much time. What has gone wrong?