How to connect two attributes without a PK (MySQL) I'm a new programmer and I'm doing a database on MySQL with phpmyadmin. I have this doubt - I know that if you want to link two different tables you need an attribute that is a Primary Key, and another in the other table that is a Foreign Key. In this particular case I want to link a normal attribute from one table to another, something like this:

Table 1: id (PK), age, birthday, name, Charge;

Table 2: id( PK), department, name, Charge (Reference);

So I can, for example, introduce the "charge" value to the first table, and then, when I visit the table 2, it would have that value as it was a FK calling a PK, or in other case, a way where I can link the PK of the table 1 with the table 2, so I can bring any attribute to it that I want like this:

Table 1: id1 (PK), age, birthday, name, Charge;

Table 2: id2, id2 (FK), department, name, Charge (Reference);

Is there any way to make this?

You're correct in that the most common way to join two tables is to use a foreign key referencing a primary key. But it is not a requirement. The SQL will work (i.e. will by syntactically correct) for any two columns you wish to use in your JOIN clause, as long as the columns' types can be coerced into something comparable. The question is, will the comparison be meaningful (i.e. semantically correct)?

As an example you may choose to store shoe size and IQ for various employees. Then the query .. inner join .. on employee.shoesize = manager.IQ is a syntactically correct statement but is (likely!) meaningless.

Also think about what it means to join two tables. Conceptually it means you take each row from the first table in turn and combine it with every row from the second table. You retain those combined rows for which the join condition (the "ON" clause) is true and discard those for which it is false.

Back to your particular example. Do the meanings of Table1.Charge and Table2.Charge allow them to be compared and still satisfy the business rules of the system? Will the values in Table1.Charge be sufficiently unique to ensure only those rows which are meant to be matched will be matched? As a counter-example, if all charges were a standard amount then every row from Table1 would match every row from Table2.

If your can answer both questions as "yes" then go for it. If not you'll have to re-design.


Why hardcoded date filter works much better than dynamic parameter? We have a stored procedure where we are getting the list of customer IDs. Within that procedure, we have a filter condition for Date. When we use hard-coded date there the query performance becomes much much better than when we use a "date parameter":

ALTER PROCEDURE [dbo].[Sp_GetCustomers]
(
    @StartDate      DATE,
    @ProductCode    VARCHAR(20) = NULL

)
AS
  BEGIN

    Select  DISTINCT c.CustomerId
    From    Custmer c
            INNER Join [Order] o On c.CustomerId = o.CustomerId

    WHERE   o.ProductCode = @ProductCode AND
            -- Filter# 1: Below hard coded date results better performance
            o.date_inserted >= '2019-06-01'
            -- Filter# 2: Below dynamic date (variable) kills the performance
            o.date_inserted >= @StartDate
END

In above query, see Filter# 1 and Filter#2

If we enable Filter# 1, it works great. But if we enable filter# 2 it kills the performance.

Any idea what could be the reason?

We cannot know for sure  without both execution plans. Below are three common reasons.

Filtered indexes

It could be the use of a filtered index such as:

        CREATE INDEX IX_Order_ProductCode_CustomerId_Filtered
        ON [dbo].[Order](ProductCode,CustomerId)
        INCLUDE(date_inserted)
        WHERE date_inserted >= '2019-06-01';

This should pop up as an unmatched indexes warning in the execution plan. More on these here

Parameter sniffing

Another common reason would be parameter sniffing, where the value that runs with the stored procedure first / on plan creation is cached for following executions, until the plan is recompiled. Recompilation can for example happen due to statistics of one of the referenced tables being updated.

More on parameter sniffing here

If you want sql server to see the parameter as a hardcoded value then you would have to add OPTION(RECOMPILE) to the query.

Wrong datatypes

if the date_inserted column is a string datatype such as varchar() then your query would not be able to seek on the parameter. It would do an implicit conversion of the varchar() field to a date field on a residual predicate instead of a seek predicate.

The difference between below two queries could then  be immense

SELECT * FROM [Order] o
WHERE o.date_inserted < '2019-06-01'

DECLARE @date date = '2019-06-01'

SELECT * FROM [Order] o
WHERE o.date_inserted < @date

With more than 2 seconds execution time on the second query

Due to the residual predicate:

where the entire date_inserted column is converted to a date datatype before filtering.


transactions and Oracle dblink I have 2 dbs, let's call them writedb and readdb.  Readdb has a dblink to writedb and a view to a table over that dblink.

I do an update on writedb to that table which view points to and then read from that table over the dblink.  I appear to be seeing old values for seconds after the transaction commits even if I set isolation level to SERIALIZABLE.
Does this make any sense?

On writedb:

create table mk.dblink_test (
id varchar2(16),
status varchar2(16));

insert into mk.dblink_test
SELECT rownum, 'ACTIVE'
FROM   dual
CONNECT BY LEVEL <= 1000000;

On readdb:

CREATE DATABASE LINK DBL_TEST CONNECT TO mk IDENTIFIED BY password USING 'writedb:1521/qa'

The SQL that updates writedb directly:

update mk.dblink_test
set status = 'DONE'
where id = '1';
commit;

Now the situation with SQL which get dirty reads is weird.  The following simpler query

SELECT r.status
FROM mk.dblink_test@DBL_TEST r
WHERE r.id = '1';
commit;

does not appear to exhibit the problem.
But a more complex version:

SELECT r.status
FROM mk.dblink_test@DBL_TEST r
LEFT OUTER JOIN (SELECT '3' status from dual) optout ON optout.status = r.status
WHERE r.id = '1';
commit;

reproduces it 90% reliably.
The steps to reproduce:

launch sqlplus and connect to the readdb
run the read query, get status 'ACTIVE'
run the update query against the writedb in a different
sqlplus
go back to the sqlplus from step (1), launch the read
query from step (2) and get 'ACTIVE' again
repeat read query same
as in step 4 -- get the correct 'DONE' response.

Oracle has spoken:

http://docs.oracle.com/cd/B19306_01/server.102/b14231/ds_txnman.htm#i1008473

  Distributed Query Returns Different Results on Consecutive Runs when
  no Changes Exist in the Base Tables. (Doc ID 561471.1)

...skip some more writing...

  Bug.611416 SELECT AFTER UPDATE AND COMMIT DOESN'T SHOW UPDATED COLUMNS

...skip some more writing...

  "You can use the following techniques to ensure that the SCNs of the
  two machines are synchronized just before a query:
  * Because SCNs are synchronized at the end of a remote query, precede each remote query with a dummy remote query to the same site, for
  example, SELECT * FROM DUAL@REMOTE.
  * Because SCNs are synchronized at the start of every remote transaction, commit or roll back the current transaction before
  issuing the remote query. "


Can I disable an account in mysql after x failed login attempts? I would like to disable accounts after x failed login attempts. Is it possible?

Also you can use fail2ban for this goal as too easily way . In this case you just install it on you linux OS, then enable the section for [mysqld-iptables] in the /etc/fail2ban/jail.local.

[mysqld-iptables]
enabled  = true
filter   = mysqld-auth
action   = iptables[name=mysql, port=3306, protocol=tcp]
       sendmail-whois[name=MySQL, dest=root, sender=fail2ban@example.com]
logpath  = /var/log/mysqld.log
maxretry = 5

This program check the mysql logs by its own given pattern and then blocks the IP addresses which they try to login more than 5 times, in iptables.


how to replace with default value when querying a JSONB column with a key that is not present in a particular row giving empty result I have a table that has a jsonb column as 'result' which has an array of objects.
Looks like this for every row:
[{"grade": "A", "subject": "MATH"}, {"grade": "B", "subject": "PHY"}...]

When I am querying this way
select up.id, jsonb_array_elements(to_jsonb(up.result)) ->>'subject' as result
from "UserProfile"

I get correct result but problem arises when I query for a particular row with an id wherein this result column has an empty array data. Then it neither gives any result nor an error even though I am selecting 'id' in query. not able to put any default value also using coalesce
How can I resolve this issue?

Use a left join:
select up.id, r.element ->>'subject' as result
from "UserProfile" up
  left join jsonb_array_elements(up.result) as r(element) on true


Why do we need weak entity As the title said, since we can convert any weak entity set to a strong one by adding the appropriate attributes, then why do we need weak entity sets?

In logical modelling one tends to record the natural keys to entity types. These will often require the "parent" and hence weak entity types are documented. Consequently they can show the scope of re-use of particular values. Take the cannonical Customer-Order-Order Line-Product example. Although the Order will have Customer Number as a foreign key it is usual for Order Numbers to be globally unique so Order is a strong entity. Often each Order's line numbers will start again at 1. By making Order Line weak we document this possibility more transparently.

In physical design we may choose to implement "weak" tables to reduce the number of joins in some queries. Continuing the example, if we make Customer Number part of the key to Order and also to Order Line then a query to count how many widgets the Jones Trading Company has ordered need only touch one table. Needless to say this involves costs in other areas and should be weighed carefully before implementation.


What versions can I have for setting up SQL Server Transactional replication Can anybody help me knowing what versions can I have replication for Publisher , Subscriber and Distributor.
Do I need to have all 3 servers as same sql server version or can I mix the versions ? what are Limitations if I have lower and higher versions and what are the recommendations?

Question 1 :
What possible SQL Versions can I have for Publisher   ?
What possible SQL Versions can I have for Subscriber  ?
What possible SQL Versions can I have for Distributor ?

Question 2:
Also, is there can I have mix of Enterprise and Standard and Express Editions for Publisher, Subscriber and Distributor?

Thanks in advance.

Yes, you can have several versions of SQL Server involved in a replication set up.  You are limited (generally speaking) to the least common denominator of functuality.  That may be just fine for you, but it is something to consider.

SQL Server 2xxx Express can only be a subscriber in a replication landscape. So as long as data only needs to move to the Express database you can use replication.

Microsoft has documented this for SQL Server 2008 R2 at: http://msdn.microsoft.com/en-us/library/ms143241(v=sql.105).aspx


PostgreSQL Streaming Replication - corruption on master We have PostgreSQL 9.3.9 streaming replication between Master DB and Slave DB.
Is there issues can happen on Master that can cause persistency problems or full failure of Master and Slave DB both while replicating? Am I always will get persistant and ready to work or restore Slave DB while replicating?

Is there issues can happen on Master that can cause persistency problems or full failure of Master and Slave DB both while replicating?

Yes, there are.

Anything that corrupts the transaction logs on the master before they're archived or streamed to the replica will do it - and I've seen that happen personally. The root cause was administrator error, in that the admin kill -9'd the postmaster, deleted postmaster.pid and then started a new postmaster up. Nonetheless, the corrupt WAL was replicated.

Memory errors that corrupt shared memory, flip a bit, etc could do it.

Severe filesystem problems affecting the transaction logs could do it.

A nasty PostgreSQL bug could do it. We actually had one of those in 9.3 too, though it's fixed prior to 9.3.9. To learn more than you ever wanted to about it, search for "multixact wrap-around".

This is why I strongly recommend that you keep point-in-time recovery using WAL archives and periodic base backups. The pgbarman utility helps with this.

Periodic text-format dumps can also be a good idea as extra insurance.

  Am I always will get persistant and ready to work or restore Slave DB while replicating?

No, because some kinds of corruption can be replicated to the slave.

The most obvious one is if you DROP TABLE critical_production_data; the system will obediently replicate that to the slave before you can even say "Oh sh...".

Point in time recovery is important.


Preparing a cluster installation fails at Setup Support Rules Step (Not Clustered or the cluster service is up and online) Today I was trying to install a SQL Server 2008 R2 Cluster environment on Windows Server 2012 R2 servers and after doing all the necessary steps to prepare the cluster (like Creating an iSCSI Virtual Disk for Windows Cluster, configuring both node members, installing .net Framework 3.5, Adding the Windows Cluster Failover Feature, configuring the cluster, etc...) I continued by running the "Advanced Cluster Preparation" installation in SQL Server 2008 R2.

During one of the initial steps (the one checking the Rules) I find that the "Not clustered or the cluster service is up and online" step fails...

Upon inspection of the details I see that the "machine is clustered, but the cluster is not online..." message

... not sure the reason why, since when I inspect the cluster using Failover Cluster Manager everything seems all right

I can even do a fail-back on the drives and a fail-over with no problem. I also ran the Validation report and everything came back green...

Any ideas of what could I be missing?

More investigation

This is what I found when looking in the SystemConfigurationCheck_Report.htm (on the bootstrap log)... apparently it is more or less the same message. All the other checks are "green" (OK).

Also, on further bootstrap log investigation, I found this section (in the detail.txt for one of my executions) that appears to shed some more light...

What "the given key was not present in the dictionary" means... I can't say. I'm not an expert in advanced cluster troubleshooting so I am only speculating here... I have updated the contents of my bootstrap log here if anybody thinks they can find some more "clues" that I have not been able to find...

More findings (24 hours later...)
So, I have not been able to find the root cause of the problem, even following suggestions in this page and this KB, nothing seems to help me with that Support Rule!

Today, out of desperation, I attempted to run the "Advanced Cluster Preparation" using the installation image for SQL Server 2017 Enterprise and that one indeed worked with no problems!

So the issue seems to be limited to SQL Server 2008 R2 cluster binaries (something that sometime after 2008 R2 was addressed). This is not a solution for me (as I need to make this work in 2008 R2 for various reasons) but at least sheds some more light into the problem (hopefully) and tells me there is nothing wrong with the Failover Cluster install and configure per se, but more of how SQL Server 2008 R2 interprets that I have a fully-functional cluster.

So, finally after more investigation I found the right post explaining my problem and the solution.

It turns out the article was the one that dco pointed out. The thing is this: the problem I was experiencing was not solved with the slipstreaming technique (the install with SP2) but with other thing mentioned there further down the road (Issue 2: The "Cluster Service verification" rule fails when you try to install a SQL Server 2008 R2 failover cluster instance).

Turns out I had to enable the "Failover Cluster Automation Server" Feature in my Windows 2012 R2 boxes.

Thanks everybody for your help on this matter, especially dco for pointing me to that website. Since the resolution that dco offered (slipstreaming with SP 3) was not the solution to my problem I did not accepted that one as the answer (so that future searches with other people looking to fix this same problem don't end up on a wild goose chase).


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

We talked about a similar issue before here and here and what worked in that case was setting the SQL Server to be dependent on the disk drivers.  You'll want to test this to make sure this works in your setup but it should do the trick.


How to do a while loop to delete rows based on rowcount? I have the following scenario:

A table containing more than a million records. I need to create a loop that deletes 10'000 rows per execution (loop) based on the column number of the rows. So if in the end of the execution the column has 500 rows for example, then i need to delete all these rows.

So I imagined that this might work:

declare COUNTER int;

while (select count(*) from Table) >= 10000)
loop
     DELETE FROM (SELECT * FROM STAGE1 WHERE STAGE1.ID <= 10000);
endloop;

It's something like this that I need, but I don't know how to increase the Counter variable in the loop. And I suppose that I need to put an IF THEN ... ELSE... statement inside the WHILE to check the SELECT * FROM STAGE1 WHERE STAGE1.ID <= 10000, and in the ELSE, delete the rest of the rows that I have: i.e the last 500 rows.

EDIT:

I'm trying to make this work:

DECLARE COUNTER INTEGER :=0;
 BEGIN
 DBMS_OUTPUT.PUT_LINE('START');
loop -- keep looping
COUNTER := COUNTER + 1;

  --do the delete 1000in each iteration
  Delete TEST where  rownum < 1000;

  -- exit the loop when there where no more 1000 reccods to delete.
  exit when SQL%rowcount <= 1000;
  commit;
end loop;
  DBMS_OUTPUT.PUT_LINE(COUNTER);
 commit;
END;

I put 10'000 rows in my TEST table. In the end the counter should be 9 and at the end of the loop the table should contain 1000 rows, but in my case the counter is 11  and the table is empty.

Why doesn't it work?

As I understand the problem, you want to leave 1000 rows in a table that starts with > 1 million.   In SQL Server, I ran I to problems with the delete statement locking the table, and also taking a long time to execute.  All the deleted records have to go into the transaction log.

What worked better was to select the records I wanted to keep into a new table, and then rename the tables.

With Oracle, your mileage may vary


SQL Server backup versus third party snapshot based backup using VSS Is it safe to assume that SQL Server instance or database level backup to a NAS share or tape using the SQL Server backup command (internally create .bak file) are transactionally consistent backups?

Your database can be online while a backup to a shared drive is in progress. How does this type of backup guarantee application consistency?

I am comparing this sort of backup against VSS-aware hardware level snapshot, and the backup option using a third party tool, which claims 100% application consistent backup. VSS performs actions like freezing the IO till snapshot is completed.

How does SQL Server native backup maintain its consistency in absence of VSS?

SQL Server uses checkpoint, log sequence numbers, and the transaction log to guarantee that a full backup is restored to a transactionally-consistent state, as of a particular point in time.

The exact point in time for a full database backup is the end of the data-reading portion of the backup process. To recover to an arbitrary point in time requires separate transaction log backups as well.

Online backup means the database remains accessible (and writeable) for other users while the backup is in progress. Enough of the transaction log is saved with the backed up database pages to enable recovery to bring the whole database to a transactionally consistent state when restored.

The basic idea is that pages saved to the backup are already consistent to the target point in time, or can be made so by undoing or redoing transactions from the included portion of the transaction log.

For full details see the TechNet article Understanding SQL Server Backups by Paul S. Randal.


MySql | How to compute every row in same table I have this table

Grade   Lec    Lab
1.50       3      0
1.50       3      0
1.50       2      1

The formula will be

1.50 * 3
1.50 * 3
1.50 * (2 + 1) = 13.5

The results will divide to total number of lec and lab it will be 13.5 / 9 = 1.5

So you want to get the total sum of grades, lec and lab and divide that by the total sum of lec and lab?

SELECT (SUM(Grade) + SUM(Lec) + SUM(Lab)) /  (SUM(Lec) + SUM(Lab))
FROM grades;

Result:

1.6667

Db<>fiddle


bind variable and shell scripting with sqlplus I wish to execute DMLs using bind variable with shell scripts.
For example, something like:

#!/bin/bash

SH_NUM=10

sqlplus -S test_user/test_pass <<EOD
var a number;
a:=${SH_NUM}
insert into test_table values(a);
commit;
EOD

I'm not sure if this is possible, I can use this approch when using pl/sql but I wish to know if I can do it this method as well.

Sure it is possible:

[oracle@o71 ~]$ cat 1.sh
#!/bin/bash

SH_NUM=10

sqlplus -S bp/bp<<EOD
set lines 220 pages 5000
col plan_table_output format a50
var a number;
exec :a :=${SH_NUM};
insert into t1 values(:a);
commit;
select * from table(dbms_xplan.display_cursor(format=>'basic'));
exit
EOD

Execute it (I just put DBMS_XPLAN.DISPLAY_CURSOR in there to see the last statement executed):

[oracle@o71 ~]$ ./1.sh

1 row created.

Commit complete.

PLAN_TABLE_OUTPUT
--------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
insert into t1 values(:a)

-----------------------------------------
| Id  | Operation                | Name |
-----------------------------------------
|   0 | INSERT STATEMENT         |      |
|   1 |  LOAD TABLE CONVENTIONAL | T1   |
-----------------------------------------

12 rows selected.


Does SQL Server / T-SQL support line-continuation to break up long strings? I sometimes have a SQL script that has one or more super-long (sometimes even stupid-long) strings. Typically these are VARBINARY literals / constants that represent files / Assemblies, but occasionally they are text.

The primary problem with really long strings is that some text editors do not handle them all that well. For example, I have a VARBINARY literal that I use in a CREATE ASSEMBLY [AssemblyName] FROM 0x.... statement, and the Assembly itself is just over 1 MB in size, which equates to just over 2 million characters in a text file since each byte requires two characters to be represented in hex notation (e.g. 0x1F = a 1 and an F). SQL Server Management Studio (SSMS) does not handle this well and hangs for several seconds as I try to scroll over that line. And in fact, some versions (not sure if this still happens) will even display a warning about long lines when opening a script that has at least one line over a certain length.

A secondary issue is that it complicates the formatting when either using in an editor without word-wrap enabled, or posting online. The problem here is that the slider for the horizontal scroll bar is very narrow and moving it even just a little bit usually scrolls the non-super-long text out of view.

Now, T-SQL doesn't terminate commands with newlines or even semi-colons (though semi-colons are preferred / recommended, starting with SQL Server 2005). So since SQL Server knows how to parse each statement such that it knows when it ends, it seems like splitting the long line across multiple lines, separated only by a newline / carriage-return + line-feed, doesn't seem unreasonable. But this doesn't work in either case.

PRINT 'Line1
Line2';

returns (in the "Messages" tab):

Line1
Line2

And that makes sense enough as the newline is within a literal / constant. But doing this for a VARBINARY also doesn't work.

PRINT 0x1234
5678;

gives me an error.

Thankfully there is support for line-continuation in T-SQL via the \ (backslash) character. Just place that at the end of of a line, right before the newline / carriage-return + line-feed, and the newline will be ignored.

For text strings, this behaves as follows:

PRINT 'Line1\
Line2';

returns (in the "Messages" tab):

Line1Line2

For binary / hex strings, this behaves as follows:

PRINT 0x1234\
5678;

returns (in the "Messages" tab):

0x12345678;

In order to format binary files (Assemblies, Certificates) into text hex-byte strings for use in SQL scripts, I wrote a command-line utility called BinaryFormatter that I have released as open-source on GitHub. Not only does it convert binary file into a text representation, but it also uses line-continuation to spread long VARBINARY literals across as many rows are needed, based on the specified number of character to use per each row. The result is something along the lines of:

4D5A09DE34F178313345A4\
00007F4E39782EFC48D842\
00000000

which I then copy and paste into my script, as shown in the {...} area below:

CREATE ASSEMBLY [AssemblyName]
FROM 0x\
{output from BinaryFormatter}
;

For additional details on this topic, please see my blog post:
Line-Continuation in T-SQL


performance issues when concurrent requests We are using SQL Server 2008 R2 on a Windows Server 2008 server.

We have some main functions which uses some Stored Procedures which has a lot of sub-queries  inside of it.

I'll take one SP as an example; when running this SP sequentially it takes about 500ms, and we are ok with it (kinda), it has mainly select queries, some on the same table, it also has some temp tables created and filled with another SP.

We are now in the process of testing these main functions under multiple concurrent requests, and we found out right away that the SP's completion time jumps really quickly to seconds and rising...

after using some suggestions, I noticed that more than one spid is associated with multiple S locks, and that some processes are blocking themselves.

other than that, I'm uncertain as to what is making performance so poor.

I'm not aware if this description is too vague, I would love to give more information if needed, in the end I would like to know where you think should I look at in order to solve this problem.

EDIT:

Here is a portion of the execution plan, it doesn't look good...

Should I just add the indexes as specified?

what does all the parallelism sections in some of the queries' execution plan mean?

does this explain the performance problem?

Looking at the query plans, the query optimizer is pretty much begging you to add useful indexes to the tables. Until the query plans are robust, forget about looking at locks and I/O.

To make solid suggestions for indexes, you pretty much need to give us a picture of the schema, all the queries, and all existing indexes on the tables. I would not take the missing index suggestions exactly as they are presented. They undoubtedly will help, but they may not be the best thing to do (the suggestions aren't usually... intelligent... for lack of a better word).

The parallelism operators mean that the query used multiple CPUs to process the query in parallel. This is only desirable when you absolutely need SQL Server to do a bunch of work -- in this case, the problem is that you're asking SQL Server to do too much work and it's doing the best it can to satisfy the query in the shortest possible time.

With a small number of rows being returned from each query with what appears to be highly selective predicates, I would expect these queries to fly with useful indexes applied (and no parallelism would be needed).

Just in the visible area, it looks like queries 9 and 10 are virtually the same for ~35% total cost apiece; relatively speaking, the table scan of Transactions should go to pretty much zero. Depending on what you're doing, you may be able to combine those two queries into one.

If that's not enough speed, there are other techniques involving precomputed aggregates, but if you're kinda-sorta-almost okay with 500ms, then you won't need to go this far with just a robust set of base query plans. If all the queries are highly selective, indexes alone should easily get you into the < 5 ms range.

Finally, consider adding useful indexes to your temp tables. A nested loops join with a table scan on the bottom input can be quite expensive depending on the data involved.


Import local data to remote server for SQL Server I currently have a machine A with SQL Server Management Studio (SSMS), and a data file on it.
A connects to machine B's SQL Server database. I have no control of machine B.
The problem is:
I can't transfer data using neither DB restore, DB attach, nor bulk import. Because for any query like
BULK INSERT [MY_TABLE_NAME]
FROM 'F:\Data\MY_TABLE_NAME.txt' WITH (
FIRSTROW=2,
FORMATFILE='F:\Data\MY_TABLE_NAME.txt.fmt')

RESTORE DATABASE MY_TABLE_NAME
FROM DISK = 'F:\Data\MY_TABLE_NAME.bak'
WITH REPLACE

SQL Server Management Studio will try to open the file in B machine, not my machine A. (I can't transfer the file to machine B.)
Is there a way to open the local file in this situation? (Technically, I can use a Python script to insert table by table, row by row, but that would be way too inconvenient.)

Use BCP, or the SQL Server Import and Export Wizard.  Both will read from local files.


MySQL Count Rows with Two Cells the same I have a db where the cells are height and width. (It's for banner ads)
Some banners are the same width, but different height, or visa versa.
for example:
row1 hieght = 600 width = 120
row2 hieght = 600 width = 120
row3 hightt = 600 width = 90
row4 height = 125 width = 125
etc

I want to know how many rows are 600x120, how many are 600x90, etc

How do I fix the sql query as follows to get the info I want?

$sql="SELECT  COUNT(`height`) GROUPED BY (`width` )
FROM  `rv_banners`
WHERE 1 " ;

What about something like:

select
  height || 'x' || width as dimensions,
  count(*) as number_of_banners
from
  rv_banners
group by
  height, width ;


How to update cust_id from Customer table to order table Cust_id field while inserting customer name using trigger I have two tables 1. Customer_details and 2. Order_details.

1.Customer_details table -->fields--> Cust_Id(Primary Key) and Customer_Name,
2.Order_details table --> fields-- Ord_Id(Primary Key), Cust_id

(There is no field for Customer name in Order_details table). I am trying to import customer orders to order_details from a CSV file through an external application.  The CSV File contains the customer name not the Cust_ID. When inserting into order_details I need to look up the Cust_ID based on the name in the file and insert the Cust_ID into order_details instead. This needs to be implemented at the Database level not in the application code. I have been searching a lot for answers but cant figure it out. Could I possibly use a trigger to accomplish this?
Thanks, Acube.

Assuming that Customer_details table is populated you can do lookups on the fly with LOAD DATA INFILE by leveraging session variables and a SET clause

LOAD DATA INFILE '/path/to/order_details.txt'
INTO TABLE order_details
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(ord_id, @customer_name) -- use a session variable to store a value read from the file
SET cust_id =
(
  SELECT cust_id
    FROM customer_details
   WHERE customer_name = @customer_name -- use a session variable to get an id
   LIMIT 1 -- make sure that exactly one row is returned
)

Suppose we have a CSV file with the following content

ord_id, customer_name
1,"Customer1"
2,"Customer2"
3,"Customer2"
4,"Customer4"

Let's try import it:

mysql> CREATE TABLE customer_details(`cust_Id` int, `customer_name` varchar(32));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO Customer_details (`cust_Id`, `customer_name`)
    -> VALUES (1, 'Customer1'),(2, 'Customer2'),(3, 'Customer3'),(4, 'Customer4');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE order_details (`ord_id` int, `cust_Id` int);
Query OK, 0 rows affected (0.02 sec)

mysql> LOAD DATA INFILE '/tmp/order_details.txt'
    -> INTO TABLE order_details
    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n'
    -> IGNORE 1 LINES
    -> (ord_id, @customer_name) -- use a session variable to store a value read from the file
    -> SET cust_id =
    -> (
    ->   SELECT cust_id
    ->     FROM customer_details
    ->    WHERE customer_name = @customer_name -- use a session variable to get an id
    ->    LIMIT 1 -- this is to ensure that exactly one is returned
    -> ) ;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM order_details o JOIN customer_details c ON o.cust_id = c.cust_id;
+--------+---------+---------+---------------+
| ord_id | cust_Id | cust_Id | customer_name |
+--------+---------+---------+---------------+
|      1 |       1 |       1 | Customer1     |
|      2 |       2 |       2 | Customer2     |
|      3 |       2 |       2 | Customer2     |
|      4 |       4 |       4 | Customer4     |
+--------+---------+---------+---------------+
4 rows in set (0.00 sec)

There you have it.


Query to find the "Number of days" a customer has an active card between a specified interval I have a table with below structure :
create table test_table
(
  customer_num      NUMBER,
  card_number       char(2),
  card_issue_date   DATE,
  card_expire_date  DATE
)

Example data is:
customer_num    |  card_number   |  card_issue_date | card_expire_date
--------------------------------------------------------------------------
  1             |  x             |  1/1/2019        | 1/4/2020
  1             |  y             |  1/1/2020        | 1/4/2020
  1             |  z             |  1/11/2020       | 1/15/2020
  1             |  b             |  1/11/2020       | 1/20/2020
  1             |  w             |  1/12/2020       | 1/17/2020
  1             |  a             |  1/18/2020       | 2/12/2020

I have a calendar table with below structure :
create table dimdate
(
  datekey   DATE,
  <other columns related to date>
)

The above table has all dates .
For each custoemr_num  we need to find the total number of days he has an active card between '1/1/2020 ' and '1/31/2020'.
We need to take some important points into consideration here :
1)We need to pay attention that for some cards such as card number x and a the issue_date might be smaller than '1/1/2020 ' or bigger than '1/31/2020'.
2)We have to pay attention to overlaps. There might be overlap between issue_date and expire_date of different cards (b,w,z,a).
First we thought that we could calculate the number of active days for each card and then subtract the overlaps from the final result and
what we wrote so far is this :
select t.customer_num,
       t.card_number,
       t.card_issue_date,
       t.card_expire_date,
       lead(t.card_issue_date) over(partition by t.customer_num order by t.card_issue_date) next_card_issue_date,
       case
         when t.card_issue_date < to_date('1/1/2020', 'mm/dd/yyyy') and
              t.card_expire_date > to_date('1/31/2020', 'mm/dd/yyyy') then
          to_date('1/31/2020', 'mm/dd/yyyy') -
          to_date('1/1/2020', 'mm/dd/yyyy')

         when t.card_issue_date < to_date('1/1/2020', 'mm/dd/yyyy') then
          t.card_expire_date - to_date('1/1/2020', 'mm/dd/yyyy')

         when t.card_expire_date > to_date('1/31/2020', 'mm/dd/yyyy') then
          to_date('1/31/2020', 'mm/dd/yyyy') - t.card_issue_date

         else
          t.card_expire_date - t.card_issue_date

       end as card_active_days

  from test_table t
 where t.card_issue_date <= to_date('1/31/2020', 'mm/dd/yyyy')
   and t.card_expire_date >= to_date('1/1/2020', 'mm/dd/yyyy')

but we could not handle the overlaps. I was wondering if you could help me here .
Thanks in advance

I think I have found a good solution for this.
Could you tell me if there are better ways other than this one?
select a.customer_num, count(distinct b.datekey) num_of_days
  from (select t.customer_num,
               t.card_number,
               t.card_issue_date,
               t.card_expire_date
          from test_table t
         where t.card_issue_date <= to_date('1/31/2020', 'mm/dd/yyyy')
           and t.card_expire_date >= to_date('1/1/2020', 'mm/dd/yyyy')) a
 inner join dimdate b
    on b.datekey between a.card_issue_date and a.card_expire_date
   and b.datekey between to_date('1/1/2020', 'mm/dd/yyyy') and
       to_date('1/31/2020', 'mm/dd/yyyy')
 group by a.customer_num


Database status "restoring..." I have opened SSMS today, and went to open my database ADMINISTRATOR (in the picture) but this option is unavailable:

What's this problem, how can I cancel this restore?
I have just added a new database, I didn't perform any restore operation for this database.

When you shutdown/restart your computer/server, the service for the SQL Server Express instance, will be triggered to stop too. While the service is shutting down the SQL Server Database Engine will try to shut down the databases gracefully, in order to ensure they can be restarted in a timely fashion when the system comes back up.
If the system was forced to shutdown/restart before the database was able to be brought to a consistent state, then the recovery will be performed automatically, when the instance is brought back up. The SQL Server Database Engine will find the corespondeing *.mdf and *.ndf file(s) and together with the existing *.ldf file try and bring the database back online.
If for some reason the database was in the middle of (e.g.) a database reindex task or reorganize then there will be a lot of outstanding transactions to either roll-forward (COMMIT) or roll-back (ROLLBACK). Until this procedure has finished, the database will be marked as RECOVERING.
If for some reason the database is unable to brought to a consistent state, then the database will switch to the RECOVERY PENDING... state.
Reference: Database States (Microsoft | SQL Docs)
Seeing as your database is neither in the RECOVERING nor in the RECOVERY PEDNING state, then we can conclude that an actual RESTORE DATABASE ... has been triggered, which results in the database displaying the state RESTORING... which should be identical to the French expression RESTAURATION....
You can double-check the state of the database by issuing the command:
SELECT sdb.name, sdb.state_desc
FROM sys.databases AS sdb
WHERE sdb.name = 'ADMINISTRATEUR';

Now if for some reason the database does not display the same state in the SELECT statement, then you might just have a simple Refresh issue.
Solution: Right-click the Bases de données in SSMS and click on Refresh
If the database state returned from the query is the same, then have a look at the ERRORLOG file of your SQL Server instance. There should be an entry, when the RESTORE started.
If not have a look at the restore history in the msdb database:
SELECT restore_date,
       destination_database_name AS NewDBName,
       destination_phys_name AS DestinationPath,
       database_name AS OrigDBName,
       server_name AS ServreName,
       physical_device_name AS PhysicalSourceName,
       rh.[user_name] AS UserName,
       'EOR' AS EOR
FROM   msdb.dbo.restorehistory rh
       JOIN msdb.dbo.restorefile rf
            ON  rh.restore_history_id = rf.restore_history_id
       JOIN msdb.dbo.backupfile bf
            ON  bf.backup_set_id = rh.backup_set_id
       JOIN msdb.dbo.backupset bs
            ON  bs.backup_set_id = rh.backup_set_id
       JOIN msdb.dbo.backupmediafamily bmf
            ON  bmf.media_set_id = rh.backup_set_id

ORDER BY 2,1

This will show you when the restore started and (if lucky) the user that started the restore.
Solution: End the restore of the database by issuing the command pointed out by Dan Guzman in his comment:
RESTORE DATABASE ADMINISTRATEUR WITH RECOVERY;

It could be that a previous RESTORE DATABASE .... WITH NO_REECOVERY was issued, which will not finalize the RESTORE. Issuing the command stops the ability to further RESTORE DATABASE ... with additional Transaction Log backups.


Failover and DNS propagation delays When a DBMS fails over after a failure, if it fails over to a server in a different and unrelated data center, then the IP address of the subdomain name of that database will need to be changed in that name's DNS server, and due to DNS propagation delays, it could be days before all the DNS servers of the world have the IP address for the new master, during which time some clients would still be trying to access to old master.  What can be done to deal with this, other than changing the client software to fetch the IP address of the master from somewhere, or is that the only option?  Thanks.

In SQL Server, the default behavior with an Availability Group listener is to register ALL IPs in all subnets with DNS. While DNS has all the IPs attached to the listener's A Record, only the IP in the correct subnet for the current primary will be online. Other IPs will be in DNS for the network name, but will not actually be online/resolvable for any actual host.
With a default configuration, multi-subnet AGs require that the clients connecting to them include MultiSubnetFailover=true as a connection string attribute. This attribute tells the driver to expect DNS to provide multiple IP addresses for the Listener name, and to try all of them to find the correct IP to connect to for that network name. Clients that do not specify this attribute will get multiple IPs and not know how to handle them properly–most drivers will pick up one of the returned IPs at random (or maybe just seemingly random), and try to connect to that. This can result in random (or seemingly random) connection failures when it picks the wrong IP.
When failover happens, the DNS records do not change. Instead, the IP that is online & responding to requests will change. The former primary IP will go offline & stop responding, and a different IP will come online in a new subnet, and start handling traffic. This is why the client-side MultiSubnetFailover=true connection string attribute is necessary. The client's driver needs to know how to handle this.
You can turn off this behavior, so that DNS only registers a single IP address at a time, or even create two listeners so that one uses the default behavior and the other registers a single IP at a time. When using the single-IP listener, you do need to wait for the TTL to expire, and for the DNS updates to propagate before clients notice the failover & are able to reconnect. In this situation, you would want to set the TTL sufficiently low that the downtime is acceptable. Adjusting the TTL is ultimately the lever you can pull to prevent the DNS propagation from taking days.


Querying MySQL InnoDB to find if the Record is in its Buffer Pool For us, it is very important to get high response time for our website php script which queries a large database. It is essentially getting user information for the user who visited the page. There may be millions of users in the db.

We would like to not bother using the user data at all if the record is not already in the InnoDB Buffer Pool of the MySQL database, and use information only if present in the above cache.

Is there a way to directly ask Mysql/InnoDB whether the record is in cache?

We thought this might have been addressed by many others when developing high performance web apps.  Is it not common to get data only if can be retrieved in super quick time (for some scenarios at least where response time is very critical) and not otherwise?

There is no built-in mechanism to query the MySQL cache without causing MySQL to bring the row into memory from disk if it is not already in memory.

You might want to consider using a caching layer between your website and the database.  Stack Exchange uses Redis; you may want to evaluate that.


Any Reason to have Regular Failover Clustering in Azure Cloud? Is there a reason to have Regular SQL Failover Clustering when database is in Azure Cloud?

I understand the benefits having Availability Groups in Azure Cloud for Database-Level protection. People can transition heavy Read queries to the secondary replica (to prevent locking-blocking, tempdb issues), offload maintenance tasks, etc.

However why would someone require Failover Clustering in Azure Cloud for Server-level protection? Don't Cloud Services inherently offer server level protection by itself? This is different to question which asks about Availability Groups: Any Reason to have AlwaysOn Failover Clustering in Azure Cloud? , this question is about FCI

Is there a reason to have Regular SQL Failover Clustering when database is in Azure Cloud?

This completely depends on your use cases, knowledgebase, requirements, and cost.

In some ways it would make sense to use S2D and setup an FCI over an AG, most notably the storage costs for high perf disk can be larger. Since each node in an AG has a whole copy of the database, it may inflate costs or may not be required to have multiple separate copies in a single site.

  People can transition heavy Read queries to the secondary replica (to prevent locking-blocking, tempdb issues), offload maintenance tasks, etc.

Be very careful with this wording, as there can still be redo blocking, ghost record cleanup, version store blowing out tempdb, and no maintenance (except checkdb and backups) can be done on the database. This will also incur extra I/O charges for the reads/writes and also for the base disk usage in Azure whereas the FCI would not.

  Don't Cloud Services inherently offer server level protection by itself? Don't Cloud Services inherently offer server level protection by itself?

Not to argue the point, but as the comments have said there can be issues with the physical location/hardware/connection that the "cloud" is running, so just by having it there doesn't mean you get to walk away without a care in the world. In Azure, for example there are different domains to make sure servers aren't taken down all at the same time for things like patching, which is an example of something that still takes place even in the cloud and isn't indicative of a failure or issue (such as hardware lost/down/etc.).

There are different levels of offerings, some are IaaS (such as this) and some are PaaS (such as AzureDB). In IaaS offerings it is basically you running your own VMs, whether in the cloud or in some datacenter you own the same applies. If this question were to be geared towards a PaaS item, then it would be incumbent upon the PaaS team to do this as you the end user won't have control over this, by choosing IaaS you're taking control over this.

By using IaaS you're responsible for configuring and managing the resources to have uptime and availability, thus it is incumbent upon you for IaaS to choose the correct cloud, OS, and software settings/configurations. Some may prove to be more expensive, tedious, etc., than others.


How to set default schema for PostgreSQL debugger? I have 3 schemas: public, meta, data.

While debugging large function from "data" schema in PgAdmin III or SQL Manager for PostgreSQL, debuggers doesn't see any tables or functions from the same schema.

Cause function is large and depends on others functions it's not effective to use raise notice or writing schema prefix.

How can I set default schema to debug it correctly? I guess public schema is default.

you should set the 'search_path' to have all schemas needed if you don't prefix them.
The default value is: "$user", public.

You should probably have: meta, data, public.

See: 18.11.1. Statement Behavior


How to Solve the Failed to initialize the CLR I Have a Following Error ..

  Failed to initialize the Common Language Runtime (CLR) v4.0.30319 with
  HRESULT 0x80004005. You need to restart SQL Server to use CLR
  integration features.

How To Fix This Error ..

You could have a bad / incomplete install of .NET 4 (I have seen it happen at least once). Try downloading and running the following repair utility from Microsoft to see if it can fix the problem:

Microsoft .NET Framework Repair Tool


Filling in date holes in grouped-by-date SQL data The actual problem involves far more data and joins, but I've created a small sample to demonstrate the issue:

-- create example table
DROP TABLE dbo.EventRecords
GO
CREATE TABLE dbo.EventRecords
    (
    EventDate datetime NOT NULL,
    EventCount int NOT NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.EventRecords ADD CONSTRAINT
    PK_EventRecords PRIMARY KEY CLUSTERED
    (
    EventDate,
    EventCount
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
-- put in some random data for example
DECLARE @Counter INT=0
WHILE (@Counter<1000)
BEGIN
    DECLARE @SemiRandomCount1 INT=@Counter*589043%23
    DECLARE @SemiRandomCount2 INT=@Counter*85907%7
    IF @SemiRandomCount1>0 AND @Counter%7<>0    -- leave some dates empty
    BEGIN
        INSERT INTO dbo.EventRecords(EventDate,EventCount)
        VALUES (DATEADD(day,@Counter,'2013-01-01'),@SemiRandomCount1)
        PRINT CAST(@SemiRandomCount2 AS VARCHAR(MAX))
        IF @SemiRandomCount2>0 AND @Counter%2=0 -- some dates have multiple entries
            INSERT INTO dbo.EventRecords(EventDate,EventCount)
            VALUES (DATEADD(day,@Counter,'2013-01-01'),@SemiRandomCount2)
    END
    SET @Counter=@Counter+1
END
--SELECT * FROM dbo.EventRecords

So, some dates have multiple entries, some have none.  I need to get results for reporting that contain every date in a specified range, with the total counts for that date (zero if there were no counts for that date).  After much googling and experimentation, I found a very clever way to generate sequences on the fly and built this function from it.  These sequences can be used to build a date sequence table on the fly which can then be used to join the EventRecords table and group by date with no holes:

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE name='GetSequence')
   EXECUTE sp_executesql N'CREATE FUNCTION GetSequence() RETURNS @Table TABLE (Value SMALLINT NOT NULL) AS BEGIN     RETURN END'
GO

ALTER FUNCTION [dbo].[GetSequence](@StartInclusive INT, @EndExclusive INT)
RETURNS @Sequence TABLE
(
    Value BIGINT NOT NULL
)
AS
BEGIN
    INSERT @Sequence
    SELECT Value=@StartInclusive+n-1
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY o1.n)
        FROM (SELECT n=ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.objects WITH (NOLOCK)) o1
        CROSS JOIN (SELECT n=ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.objects WITH (NOLOCK)) o2
        CROSS JOIN (SELECT n=ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.objects WITH (NOLOCK)) o3
        CROSS JOIN (SELECT n=ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.objects WITH (NOLOCK)) o4
        CROSS JOIN (SELECT n=ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.objects WITH (NOLOCK)) o5
        CROSS JOIN (SELECT n=ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.objects WITH (NOLOCK)) o6
        ) D (n)
    WHERE n<=@EndExclusive-@StartInclusive
    RETURN
END
GO

Here are sample queries:

DECLARE @StartDate DATE='2013-01-01'
DECLARE @EndDate DATE='2015-01-01'
-- query with holes: not what I need
SELECT [EventDate], [TotalEventCount]=ISNULL(SUM(EventCount),0)
FROM dbo.EventRecords
GROUP BY [EventDate]
-- query with date holes filled in: this is what I need
SELECT
    [EventDate]=DATEADD(day,s.Value,@StartDate),
    [TotalEventCount]=ISNULL(SUM(EventCount),0)
FROM [dbo].[GetSequence](0,DATEDIFF(day,@StartDate,@EndDate)) s
LEFT JOIN dbo.EventRecords c
    ON DATEDIFF(day,@StartDate, EventDate)=s.Value
GROUP BY s.Value

So, my question is: is there a better (simpler or faster) way to get sequences, or a better way to solve this problem in SQL?

I recommend using a Calendar table or Date Dimension (whichever name you prefer). Here is an answer with using a quick CTE.

/* Date Range CTE */
-- Updated based on @AaronBertrand's articles linked in the comments
-- Basically ends up being the same query as the last half of @AaronBertrand's post
declare @FromDate date;
declare @ThruDate date;
set @FromDate='2013-01-01';
set @ThruDate='2015-01-01';

with cal as (
  select top (1+datediff(day, @FromDate, @ThruDate))
      DateValue = dateadd(day, v.number, @FromDate)
    from master.dbo.spt_values v
    where v.type = 'P'
      and v.number >= 0
    order by v.number
  )
select EventDate = cal.DateValue
     , TotalEventCount = isnull(sum(EventCount), 0)
  from cal
    left join dbo.EventRecords e on cal.DateValue=e.EventDate
  group by cal.DateValue
  order by cal.DateValue

Some links about calendar tables:

Sql 2005 Calendar Tables - Why You Need One - David Stein
Sql 2008+ Creating a Date Table/Dimension on SQL 2008 - David Stein
Calendar Tables in T-SQL


Postgresql: How do full_page_writes help prevent data loss? From the postgresql docs

  [full page writes are] needed because a page write that is in process during an operating system crash might be only partially completed, leading to an on-disk page that contains a mix of old and new data. The row-level change data normally stored in WAL will not be enough to completely restore such a page during post-crash recovery. Storing the full page image guarantees that the page can be correctly restored...

How come the WAL logs are not enough to do a full restore? My understanding is that they contain all page updates since (atleast) the last checkpoint.

Can someone give an example of how simply replaying the WAL logs from the last checkpoint could still result in data loss?

Many (most?) redo records actually need the block to be in some reasonable state in order to replay into it.

Consider:

Not all WAL records are row-level.  Say you read and replay a WAL record that says "I defragmented this block according to the usual rule of defragmenting blocks".  This would be a deterministic operation given the correct starting state of the block, but given a mixture of blocks states it is not deterministic and so cannot be replayed safely.  It might be possible to enumerate which WAL record types are of this type, and only have those types lead to FPW, but that seems brittle.
If a block did suffer a torn write, I for one would not want to rely on all the bytes in that block being exactly either the old state or new state, in precisely two contiguous chunks.
If the WAL could not be read entirely to the end (and how would one ever know if it was?) then there may be some blocks that have "new" data which was in fact "future" data, for a future which no longer exists.  In theory this should never happen, as the WAL was synced to disk before the block data was released.  But in the nanoseconds when a crash is evolving but has not yet manifested, who knows what could happen?  Better to lose a few transactions that were reported to have been committed, then to corrupt the system entirely.


How to check if the current connection is in a transaction? I need my program code to ensure that certain part of logic is being executed within a transaction.

What query would tell me the current transaction ID  / other information that would allow me to determine if I am in a transaction from within the transaction?

BEGIN;
-- How to check if I am in a transaction?
COMMIT;

A simple way is to compare now() to statement_timestamp().

now() gives the current date and time (start of current transaction).
statement_timestamp() gives the current date and time (start of current statement).

Example:

SELECT now() = statement_timestamp();
-- TRUE

BEGIN;
SELECT now() = statement_timestamp();
-- FALSE

The other alternative is to execute two queries:

SELECT txid_current();
SELECT txid_current();

and compare the resulting xid. If it is the same, then you are in a transaction.

The downside to the latter approach is that every txid_current() increments xid value and will further advance you to a wraparound.


Oracle : Is there a way to grant the ability to run a job to a role? We have several dbms_scheduler jobs in an 11.2 database that we would like users with a certain role to be able to run.  As such, we don't want to grant the ability to run directly to the users, as it will be more difficult to maintain.

Based on Oracle's documentation, granting ALTER JOB to the user will allow them to run the job.  Unfortunately, it doesn't appear this extends to roles: I can run the GRANT statement just fine, and the job shows up in ALL_SCHEDULER_JOBS for the user, but attempting to run the job results in the following error:

ORA-27476: "S1.J1" does not exist
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 1

Given that DBMS_SCHEDULER is AUTHID USER, but DBMS_ISCHED is AUTHID DEFINER, am I just out of luck?  Or is there a way I can grant it to the role and not the user?

This sounds like the usual problem with stored procedures: when they are compiled by developer A in his schema, the compiler only considers the privileges DIRECTLY granted to A - as if the roles were all stripped off before compiling.  This is often addressed by writing your procedure as AUTHID CURRENT_USER, but if the procedure calls another procedure granted through a role, it can fail.

Generally it seems anything you want to compile into a PL/SQL object has to be directly granted to you.  I've written a number of scripts to turn role-granted privileges into direct GRANT statements to the procedure owner so that it would compile.

Note this does not apply to anonymous blocks - they compile and run just fine with the benefit of your current roles' privileges.

So I think the scheduler is another case where you have to code as if the roles weren't there.  It's a pain and I hate it, but that's how it is.


How to keep MySQL general_log after I restart my computer? I use a statement set global general_log = 'ON' to enable the general_log on my computer.

After I restart or shutdown it, I have to run the statement again.

How can I config MySQL write general log without to run the statement again and again?

Add this to my.ini

[mysqld]
log

If you have a specfic place for the log, such as C:\mysql_general.log

[mysqld]
log=C:/mysql_general.log
log="C:\mysql_general.log"

It's one of those. I haven't done mysql in Windows in a while.

Then restart mysql

net start mysql

Give it a Try !!!

CAVEAT

Prior to MySQL 5.6, you can use log.

In MySQL 5.6, log is deprecated. Use general-log instead.

UPDATE 2011-12-09 22:18 EDT

If you do not have my.ini defined, then you must define one.

To keep things simple, just create my.ini in the folder where my-huge.ini, my-large.ini, my-medium.ini, and my-small.ini appear. Just open up notepad on my.ini in that folder.

Then add the lines I mentioned before:

[mysqld]
log

or

[mysqld]
log=C:/mysql_general.log
log="C:\mysql_general.log"

UPDATE 2011-12-09 22:26 EDT

In the worst case, try putting my.ini in C:\


Taking backup of all the databases in SQL Server I have a Microsoft SQL Server 2005 DB server. In the DB server, I have around 250 user databases. I have to take a back up of all these databases. Since, manually taking backup consumes lot of time, I am looking for a Batch script or DB script which will automatically take the backup of all the 250 databases. Can anyone please help on this?

Note: First create a folder on D: drive. (e.g. D:\User_DataBackup\)

Step 1: Create a procedure that are given below.

Create PROCEDURE [dbo].[UserDataBaseBackUp]
AS

BEGIN
SET NOCOUNT ON;

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'D:\User_DataBackup\' -- as same as your created folder'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),104)
DECLARE db_cursor CURSOR FOR
  SELECT name
  FROM MASTER.dbo.sysdatabases
  WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
  BACKUP DATABASE @name TO DISK = @fileName
  FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
END

Step 2: Execute the above procedure.

 EXEC [UserDataBaseBackUp]

You can also schedule this procedure. This procedure is tested hopefully it will help.


I have connection issue and its changing ip I use DB 'Mysql 14.14 Distrib 5.7.24' and the OS is 'Ubuntu 18.04'

The DB server have local IP : 10.0.211.198

And For The application IP : 10.0.214.198

when I try connect form application server to db I get error in the connection but After I read it I found the mysql client change IP to Application IP as below screen

But when I try connect using telnet its successfully connected.

I try to remove db and reinstall it also try different client to connect and its same result.

Thank you

I see 2 attempts where 'root' is expected to have a password, then two where it is not.  Did someone change things meanwhile?

It is not "normal" to allow root to come in any way other than via "localhost".  Did someone GRANT access for root from elsewhere?  If not, then should you really be logging in as "root"??

telnet checks the connection.  However, that is not in question -- "Access denied" came from the server back to the client, hence the connection succeeded.


Querying All Records of All Users who have any instance of purchase behavior in their history I am trying to write a query that shows me all user ids that in any instance of usage have a particular type of "purchase credit" associated with their account.  I have one table, "users" that shows user information, ie, user ids, names, birthdate, etc. and then I have another table that shows all users all instances of credit purchases.  I want to pull all credit purchases (not just the type I am looking up) from all users who in any instance of purchase have this one kind of credit purchase in their record.  Any help would be much appreciated!

It's common practice to write this sort of query using an exists construct, something like:

select *
from users join credit_purchases using (user_id)
where purchase_type = 'A'
and exists (
    select 'x'
    from credit_purchases
    where purchase_type = 'B'
    and user_id = users.user_id
);

Be careful that this type of query only works properly if you're looking for different records in the sub-query; if it is at all possible to "hit" the same record, you need to exclude that in the subquery's where clause, by doing something like:

select *
from users join credit_purchases cp using (user_id)
where purchase_type = 'A'
and exists (
    select 'x'
    from credit_purchases
    where purchase_type = 'A'
    and user_id = users.user_id
    and credit_purchase_id <> cp.credit_purchase_id
);

I'm assuming your tables have surrogate keys with the obvious names.


Visual Studio SSDT Ignore Query Store Options in Publish Profile Is there any way in Visual Studio SSDT Publish Profile, to Totally Ignore Query Store Options Completely?

It keeps asking us to Rerun publish code below. I go to Database advanced settings, and turn it off. Even after its set off, it keeps asking to be in publish profile. Is there any way to Just Ignore Query Store in Publish profile, rather than trying to just turn it off?

I am looking through advanced options in publish profile.

BEGIN
    ALTER DATABASE [$(DatabaseName)]
        SET QUERY_STORE (QUERY_CAPTURE_MODE = NONE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 367))
        WITH ROLLBACK IMMEDIATE;
END

There does not seem to be a way to ignore individual database properties. However, you can ignore them all by adding the following to your publish profile XML file:

<ScriptDatabaseOptions>False</ScriptDatabaseOptions>

I have tested this and it does indeed leave out the QUERY_STORE statements.

Then, if there are any database-level properties that you do want set, you can add them back in manually via a Post Deploy SQL script that is added to the project and tagged as being PostDeploy in the file properties, under the "Build Action" property drop-down.

You can also add that XML element to your .sqlproj file or your .sqlproj.user file.

This option needs to go within a <PropertyGroup>, regardless of which of the three files you put it in. The order of them items within the <PropertyGroup>...</PropertyGroup> don't matter.

Another option, if you are merely generating the "Create" or "Publish" SQL script and are not deploying it immediately, is to use a utility to find and replace the two SET QUERY_STORE... lines with something like:

SET {some other DB property} = {its current setting}; --

For example:

SET TRUSTWORTHY OFF; --

Which should result in those two lines being turned into:

SET TRUSTWORTHY OFF; -- (QUERY_CAPTURE_MODE = NONE, ...

You might want to look into the following dos-based RegEx processor:

JREPL.BAT - regex text processor with Unicode and XRegExp support


Create Standby database using SE We are on 11.2.0.1.I have been trying to create a Manual Standby database using Standard Edition.

Although at this point its more like an incomplete recovery.

This is wat i did

Create Standby control file on the source

Backup source(db+arc).

Restore on target.(Followed pretty much the standard RMAN restore procedures)

Am able to mount the instance and apply the most recent ARC generated on the source.But when I try to  open I get the below error

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 03/21/2014 14:02:12
ORA-01666: control file is for a standby database

This is what i see on my alert log

ORA-279 signalled during: alter database recover logfile '/u03/oracle_recovery/flash_recovery_area/XX/1_11478_723733449.arc'...
alter database recover cancel
Media Recovery Canceled
Completed: alter database recover cancel
Fri Mar 21 09:07:25 2014
alter database open resetlogs
ORA-1666 signalled during: alter database open resetlogs...
Fri Mar 21 09:07:57 2014
alter database open resetlogs

Reading up on some posts over the internet I tried to open the DB in RO mode

alter database open read only
Beginning standby crash recovery.
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Media Recovery Waiting for thread 1 sequence 11479
FAL[client]: Error fetching gap sequence, no FAL server specified
Fri Mar 21 09:11:21 2014
Standby crash recovery need archive log for thread 1 sequence 11479 to continue.
Please verify that primary database is transporting redo logs to the standby database.
Wait timeout: thread 1 sequence 11479
Standby crash recovery aborted due to error 16016.
Errors in file /u01/app/oracle/diag/rdbms/xx/xx/trace/xx_ora_21943.trc:
ORA-16016: archived log for thread 1 sequence# 11479 unavailable
Recovery interrupted!
Completed standby crash recovery.
Signalling error 1152 for datafile 1!
Errors in file /u01/app/oracle/diag/rdbms/xx/xx/trace/xx_ora_21943.trc:
ORA-10458: standby database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup

I understand that it is requesting the next archive sequence 11479.This is not yet generated on the source.

Can anyone tell me why 'Open resetlogs' failed in the first place ?

Is this the behaviour when we create a 'Standby' control file ?

Thanks,

There is some misunderstanding here.

Your alert log shows that you were trying to use Data Guard features, this is clearly not a manual standby.

A manual standby is a copy of the "primary" database continuously rolled forward manually with the archive logs from the "primary" database.
You transfer the archivelogs with OS commands, e.g. scp or rsync. Then you periodically register the archivelogs and recover the database with RMAN or SQL*Plus user managed recovery, but definitely not the Data Guard Media Recovery Process.
Data Guard is not involved at all, so there is no need for a standby controlfile, just a regular controlfile.


Wildcard in CREATE grant in MySQL MySQL allows the use of wildcards for database names, in order to allow an user to operate only on a subset of databases:

GRANT ALL PRIVILEGES ON `foobar%`.* TO 'user'@'%' IDENTIFIED BY 'somepassword';

Is there a way to do the same for the CREATE grant, to allow (cf. the example above) user to create only databases whose name starts with foobar?

Otherwise said: is the CREATE grant global (i.e. an user with this privilege is allowed to create any database, without limitations) or it can be limited in some way?

Yes. Just add the CREATE privilege:

GRANT CREATE ON `foobar%`.* TO 'foobaruser'@'%' IDENTIFIED BY 'foobarpass';

And just test it:

foobaruser$ mysql
mysql> create database `foobar_one`;
Query OK, 1 row affected (0.00 sec)

mysql> create database `barfoo_one`;
ERROR 1044 (42000): Access denied for user 'foobaruser'@'localhost' to database 'barfoo_one'

Be aware that you need to escape the _ (underscore), as it acts like one character in the pattern. So «`foobar_`» will match foobar1 or foobarZ.


Is changing the SQL Server port really that much safer? SQL Server's default port is 1433. I've been told by our admin that the port has to change "for security reasons".

Is it really that much safer to change the port? If the server is behind a firewall and it only allows connections to it from a certain range of IP's, is that not good enough?

It helps against common port scans that can be initiated through port scanning websites. But it won't help against a committed attacker. It's just another layer, but doesn't add much over the firewall, as you mentioned.


Setup Master-Slave replication with DML changes in Slave I have a master database (PostgreSQL 9.0) and I need to setup a streaming replication on a slave. Slave db can read/write and make schema level changes too which shouldn't write back to master. Can you please highlight how can I do this?

If you only use Streaming replication, it doesn't support the write operation on the slave node. Currently,  PostgreSQL  doesn't support  multiple master itself, If you want multiple master solution,  I suggest you use PostgreSQL-XC.


MySQL query to search string in Master-Child tables and return all rows with Master-Child relation Need MySQL query Help.

Please find schema below:

Table1
PK_id    Table1_Title    Table1_Description
1        Test1           This is Test1
2        Test2           This is Test2

Table2
FK_id    Table2_Title    Table2_Description
1        Test2           This is Test2
1        Test3           This is Test3

Here "Table2" is in child relationship with Master table "Table1". I want to search in Title and Description columns of above 2 tables. If search string is found in master table "Table1" then that row along with all the rows of child should be returned. Simillarly if search string is found in one of the child tabel row then the coresponding Master table row and all its child should be returned. To clarify please find result below:

Result:

Case 1:
If searching TEST2 Then

PK_id    Table1_Title    Table1_Description    FK_id    Table2_Title    Table2_Description
1        Test1           This is Test1         1        Test2           This is Test2
1        Test1           This is Test1         1        Test3           This is Test3
2        Test2           This is Test2         NULL     NULL            NULL

Case 2:
If searching TEST1 Then

PK_id    Table1_Title    Table1_Description    FK_id    Table2_Title    Table2_Description
1        Test1           This is Test1         1        Test2           This is Test2
1        Test1           This is Test1         1        Test3           This is Test3

Case 3:
If searching TEST3 Then

PK_id    Table1_Title    Table1_Description    FK_id    Table2_Title    Table2_Description
1        Test1           This is Test1         1        Test2           This is Test2
1        Test1           This is Test1         1        Test3           This is Test3

Is it possible to have a result with single query or something else?

Please help.

SELECT  t1.*,
        t2.*

FROM    Table1 AS t1
    LEFT JOIN
        Table2 AS t2
            ON WHERE t2s.FK_id = t1.PK_id

WHERE   t1.Table1_Title = 'search text'
   OR   EXISTS
        ( SELECT  *
          FROM    Table2 AS t2s
          WHERE   t2s.FK_id = t1.PK_id
            AND   t2s.Table2_Title = 'search text'
        ) ;


slow disk with InnoDB page compression I have a write-intensive MariaDB with both NVMe SSD and HDD disks. I recently enabled page compression (innodb_compression_default=ON). I encountered two problems:

The database gets slow after a while. INSERTs get noticeably slower after one day. When restarting the machine or MariaDB, it gets backs to normal speed.

It takes a long time to shutdown MariaDB (~30 min). During this time, the SSDs have heavy disk activity (as checked by iostat). Note that it is not flushing the data into the disk. Before shutting down, the number of dirty pages is zero, and there is absolutely no activity in show engine innodb status \G.

I think it might be due to the rearrangement of data on the SSD drives (about 50% full). Some sort of activity like SSD TRIM.
How can I diagnose the underlying causes?
This is part of my config, which might be relevant
key_buffer_size     = 20M
max_allowed_packet  = 5G
thread_stack        = 256K
thread_cache_size       = 8
innodb_buffer_pool_size = 50G
innodb_log_buffer_size = 128M
innodb_log_file_size = 10G
innodb_thread_concurrency = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_flush_log_at_trx_commit = 0
innodb_compression_level = 6
innodb_autoinc_lock_mode = 0
innodb_io_capacity=2000
innodb_io_capacity_max=30000
innodb_max_dirty_pages_pct=0
innodb_doublewrite = 0
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth=128
tmp_table_size=2G
max_heap_table_size=512M
innodb_purge_threads=8
innodb_purge_batch_size=600
innodb_change_buffer_max_size=50
innodb_buffer_pool_load_at_startup=OFF
innodb_buffer_pool_dump_at_shutdown=OFF
innodb-fast-shutdown=0
innodb-ft-result-cache-limit=4G
innodb_compression_default=ON

I face a similar issue with MariaDB when using page compression.
My inserts would be fast, but after a few hours, inserts would 'freeze' for many seconds/minutes at a time.
I tried tweaking the innodb_log_file_size and set it to a higher value (like
56GB) and it reduced the number of times would happen.
https://mariadb.com/kb/en/innodb-system-variables/#innodb_log_file_size
You can also try tweaking innodb_io_capacity and innodb_io_capacity_max
https://mariadb.com/kb/en/innodb-page-flushing/
I got these recommendations from AWS RDS support team.


“Column does not exist” with INNER JOIN and MAX query in ORDER BY I'm using PostgreSQL 9.6 with ActiveRecord. I'm trying to get all customers sorted by a specific provider. The relationship between models is: customer has many orders, which belong to a provider.

What I want to achieve is something like:

Example data:

customer1 <- order1 -> provider1
customer2 <- order2 -> provider2
customer3 <- order3 -> provider3

Expecting result:

query_for_provider(2) => customer2, customer1, customer3
query_for_provider(1) => customer1, customer2, customer3
query_for_provider(3) => customer3, customer1, customer2

I wrote the following query:

SELECT "customers".*, MAX(providers.id) AS provider_id FROM "customers"
INNER JOIN "orders" ON "orders"."patient_id" = "customers"."id"
INNER JOIN "providers" ON "providers"."id" = "orders"."provider_id"
GROUP BY customers.id
ORDER BY @(2 - provider_id);

Unfortunately, it returns an error:

column "provider_id" doesn't exist

When I wrap this with SELECT * FROM (as below) it works, but in this case, can't use this with ActiveRecord, which requires SELECT ... FROM customers construction.

SELECT * FROM (
  SELECT "customers".*, MAX(providers.id) AS provider_id FROM "customers"
  INNER JOIN "orders" ON "orders"."patient_id" = "customers"."id"
  INNER JOIN "providers" ON "providers"."id" = "orders"."provider_id"
  GROUP BY customers.id
) AS customers_with_provider
ORDER BY @(2 - provider_id);

Any thoughts how to achieve this?

To sort a given provider_id first, this is cheaper:

SELECT c.*, MAX(o.provider_id) AS provider_id
FROM   customers c
JOIN   orders    o ON o.patient_id = c.id
GROUP  BY c.id
ORDER  BY MAX(o.provider_id) <> 2        -- to list provider_id 2 first
        , MAX(o.provider_id);            -- to sort the rest ascending

Works because FALSE sorts before TRUE.
Add provider_id as 2nd ORDER BY item to sort the rest in ascending order.
(NULL sorts last in ascending order in any case.)

Note that your original expression @(2 - provider_id) also affects the sort of the rest since it folds values above and below the given provider_id. Makes it impossible to sort the rest in ascending order like you requested - and is therefore wrong on principal.

Assuming that referential integrity is enforced by a FOREIGN KEY constraint to providers (the typical setup), you don't need to include that table in the query at all. Faster.

Related:

Select rows for which at least one row per set meets a condition
Sorting null values after all others, except special

If that involves most or all of table orders it's faster to aggregate first and join later:

SELECT c.*, o.provider_id
FROM   customers c
JOIN  (
   SELECT patient_id, MAX(provider_id) AS provider_id
   FROM   orders
   GROUP  BY 1
   ) o ON o.patient_id = c.id
ORDER  BY o.provider_id <> 2, o.provider_id;

Why the error?

column "provider_id" doesn't exist

You tried to reference the output column name provider_id in the expression @(2 - provider_id) But expressions in the ORDER BY clause can only be formed from input columns. The manual:

  Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values.

Bold emphasis mine. Postgres couldn't find provider_id in the list of input column names and responded with the error you quoted.


Why - read but not update ops fail in case of lacking space in tempdb database? MSDN online article "Choosing Row Versioning-based Isolation Levels" states:

"tempdb must have enough disk space for the version store. If there are very long-running transactions, all the versions generated by update transactions during the time must be kept in tempdb. If tempdb runs out of space, update operations do not fail, but read operations using row versioning might fail"

Can you explain me why?
taking into account that (quoting the same article):

"When either the READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database options are ON, update and delete transactions for a particular database must maintain row versions even when there are no transactions using a row versioning-based isolation level... Because the record versions are stored in tempdb"

that is, update (but not read) operations require space for row versioning should have IMO failed due to lack of space.
And why do read ops fail if they do not require version storage in tempdb ?

(Converting my comment to an answer. I don't know if this is definitive, but it makes sense to me. If you can prove it wrong, let me know in the comments.)

The row versions aren't actually required to complete a write operation; they're only required by readers that come along and need those versions of the rows. If they need them.

If tempdb runs out of space during version generation, while that's not okay, it may end up working out if no readers need those versions. A read operation would only fail if it needs a version of a row that isn't in tempdb.

Furthermore, any write operation is considered more important than a SELECT, for obvious reasons (same idea as when the Deadlock Monitor figures out which process to kill if there's a deadlock), so it's preferable to fail a SELECT if possible.


Mongo Update keeps adding to the the size of the collection I am running a scraper to check for this one thing to change. So I have no idea why my mongo data base keeps growing when all I do is an update on this single data.

I only have one item in the database, with a post_id : 1. In my code all I am doing is updating it. (python)

client = MongoClient(DB_URL)
db = client.chatScrape

collection = db.posts

#insert scraped data
result = collection.update({'post_id' : 1}, scraped, upsert = True)

client.close()

So after the first execution I get

> db.posts.find().count()
> 1

But after an hour or so I get

> db.posts.find().count()
> 2

After about a week or so, the number grows to 10+. So what is going on here. Shouldn't update only modify one item and not make more?

I'd hazard a guess that your code is running the upsert command fairly quickly. It may depend on your MongoDB version, but on the latest version an update command which does not specify update modifiers will just overwrite the entire document, which involves deleting and re-writing it.

It seems likely that in between another upsert operation, a second upsert is run and when it sees no document it inserts a new one. In this case you might want to look at not updating the entire document.

You could also look into using a unique index and a little re-try logic which might work for you based on how infrequently this appears to be happening.


Should I convert empty varchar values to NULL? I have a stored procedure which accepts various varchar parameters. The middle tier code which calls the procedure has not been consistent in terms of the values it submits. For example, sometimes a parameter like "Transport Description" will be submitted as NULL and sometimes it will be an empty string.

In terms of the client, an empty string simply means a value was not entered.

Should I convert all the empty varchar parameters to NULL before inserting the data?

I can't decide if this falls into the realm of a best-practice to preserve the integrity of the data or a bad idea because I'm not representing what was actually submitted to the database.

I should note that all inserts and updates are tracked in an audit log.

As a DBA, your sacred duty is to take whatever data was entrusted to you, and leave it as is.

A NULL in a field usually means "DATA was not entered" or "DATA is not applicable" (but can have other meanings - it is context specific), and may actually MEAN something to the business layer. An empty string may actually be what the user entered and WANTS. Of course, I am generalizing - possibly the reason you get either NULLs or '' (empty strings) is due to some sloppy coding in the business layer (let us hope not, though).

Either way, have a chat with whoever provides the Middle Tier and see what they are using NULL vs Empty String for - and then go from there. Maybe they are thinking that they always pass in an Empty String (or always NULLS) and are not aware of the randomness (in which case advise them on what you think should be the standard), or maybe NULL really is information to them


POWA analyzer displays error 500 after logging in I tried to install POWA web analyzer. I can see the login page but after login there is a 500 error message.
This is the error at the server:
ProgrammingError: (psycopg2.errors.InsufficientPrivilege) permission denied for table powa_servers

    [SQL: SELECT
           CASE WHEN id = 0 THEN
              '<local>'
           ELSE
              COALESCE(alias, hostname || ':' || port)
           END AS alias,
            error
            FROM powa_servers s
            JOIN (SELECT srvid, unnest(errors) error
                FROM powa_snapshot_metas
                WHERE errors IS NOT NULL
            ) m ON m.srvid = s.id]
    (Background on this error at: http://sqlalche.me/e/13/f405)

I granted all permissions to powa user in postgres and loggin in using this user.

There are additional requirements for database objects documented at https://powa.readthedocs.io/en/latest/security.html.
Let me know if there are any problems after applying those.


Can SQL Server create collisions in system generated constraint names? I have an application which creates millions of tables in a SQL Server 2008 database (non clustered). I am looking to upgrade to SQL Server 2014 (clustered), but am hitting an error message when under load:

“There is already an object named ‘PK__tablenameprefix__179E2ED8F259C33B’ in the database”

This is a system generated constraint name. It looks like a randomly generated 64-bit number. Is it possible that I am seeing collisions due to the large number of tables? Assuming I have 100 million tables, I calculate less than a 1-in-1-trillion chance of a collision when adding the next table, but that assumes a uniform distribution. Is it possible that SQL Server changed its name generation algorithm between version 2008 and 2014 to increase the odds of collision?

The other significant difference is that my 2014 instance is a clustered pair, but I am struggling to form a hypothesis for why that would generate the above error.

P.S. Yes, I know creating millions of tables is insane. This is black box 3rd party code over which I have no control. Despite the insanity, it worked in version 2008 and now doesn’t in version 2014.

Edit: on closer inspection, the generated suffix always seems to start with 179E2ED8 - meaning the random part is actually only a 32-bit number and the odds of collisions are a mere 1-in-50 every time a new table is added, which is a much closer match to the error rate I’m seeing!


Having 2 identities in a table I need to have some how 2 identity columns in one table. One that is normal identity and another one that will reset yearly.

I have done the reset part with the help of DBCC CHECKIDENT ('TestTable', RESEED, 0) in the insert procedure.

But I don't know how to have a normal identity besides this one. Solution may be using two tables but I prefer to have both of them in a single table.

It looks like the comments may have answered this for you, but so there's an actual answer:

You cannot have two columns in one table that both have an IDENTITY(n,m) specification.

The most sensible approach for your requirement is probably:

your normal always-incrementing identity column should have the IDENTITY specification
use a SEQUENCE (see here for reference documentation and here for more general guidance) to control the other column that should reset each year.

You can restart a sequence on demand using

ALTER SEQUENCE <sequence_name> RESTART WITH <new_start_value>;

This command could be issued in a number of ways, for example from a job scheduled to run yearly on your desired reset date.


SSIS Excel Import - First row getting skipped I am using VS 2017(SSDT) with SSIS(2012). I'm trying to import an Excel file, but the issue is that the first row is getting skipped.
I've unchecked First row has column names in Excel Connection Manager, but in vain.
Please advice on how to import the first row.

This is very easy using the Wizard:

In your Project, drag a data flow task onto the board

Double-click the data flow task. being inside the data stream, drag an excel source.

then we create a new connection to excel by clicking on excel source.

we look for the excel file that we want to import and select: "the first row has column names"

we select the table or the view that we have in the excel sheet.

With this we would already have the process to import the excel data to any destination ...

good Luck...


Count and sum, counted and summed values I know the title is a bit mysterious. I just would like to ask how I can optimize this query to work better

I have orders and order_items. Every order_items has order_id from "orders" table so that same order_id can be in various order_items since in and order someone can purchase more than one order_item

I reached my goal with this query. But I think it could have been written better, more optimized.

My goal is to find distinct number of order_ids and total amount of all order_items associated with these order_ids

SELECT
  COUNT(*)       AS total_orders,
  SUM(total_invoice_amount) AS total_amount
FROM
  (
    SELECT
      SUM(invoice_amount) AS total_invoice_amount
    FROM
      order_items a
    GROUP BY
      order_id
  ) b;

My goal is to find distinct number of order_ids and total amount of all order_items associated with these order_ids

You can get same result using a single query:

SELECT
  COUNT(DISTINCT order_id) AS total_orders,
  SUM(invoice_amount) AS total_invoice_amount
FROM
  order_items;


Unable to create Functions/Store Procedure Azure mySQL I have just set up a MySQL database with Azure and I am using Navicat to connect to it and everything is working fine. I am now trying to create some Functions and Store Procs but I am getting a Super User Error:
You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) > Time: 0.027s

Here is My Code for one of them:
CREATE function `triggertradersazure`.`sf_entity_active_sub`(`p_entity_id` INT)
returns INT(11)
DETERMINISTIC
begin
  IF EXISTS (SELECT 1
             FROM   tbl_stripe_customer_link_sub sc
                    JOIN tbl_stripe_sub_payments ss
                      ON sc.stripe_customer_id = ss.stripe_customer_id
                         AND ss.stripe_subscription_id =
                             sc.stripe_subscription_id
             WHERE  active = 'Y'
                    AND entity_id = p_entity_id) THEN
    RETURN 1;
  ELSE
    RETURN 0;
  end IF;
end;

User Info from mql.user:
INSERT INTO `mysql`.`user`
            (`host`, `user`, `password`, `select_priv`, `insert_priv`, `update_priv`, `delete_priv`, `create_priv`, `drop_priv`, `reload_priv`, `shutdown_priv`, `process_priv`, `file_priv`, `grant_priv`, `references_priv`, `index_priv`, `alter_priv`, `show_db_priv`, `super_priv`, `create_tmp_table_priv`, `lock_tables_priv`, `execute_priv`, `repl_slave_priv`, `repl_client_priv`, `create_view_priv`, `show_view_priv`, `create_routine_priv`, `alter_routine_priv`, `create_user_priv`, `event_priv`, `trigger_priv`, `create_tablespace_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`, `max_questions`, `max_updates`, `max_connections`, `max_user_connections`, `plugin`, `authentication_string`, `password_expired`)
VALUES      ('%', 'triggertraders', '*', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'Y', 'N', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', '', '', '', '', 0, 0, 0, 0, 'mysql_native_password', '', 'N');

This user does not have super user but I should not need it and Azure doesn't allow it?
Not sure what to do with this at the moment. My function had
`CREATE DEFINER = `tiggertraders`@`ip`

I removed this as this is meant to fix the problem.
Any help would be great,

I seem to have found the awnser - I have had to change the below setting in the server parameters.


Is it possible to restore a database with only database roles membership? I have an existing database, that i want to restore from a backup by overriding the existing database.

at the moment I have

db_owner
db_backupoperator

roles

I thought that would be enough to restore the db. However when i try i get the error

User does not have permission to RESTORE database 'XY'

Is this achievable just with granting database roles (and without granting server role and fine tuning permissions?)

It is not.  See https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-ver15#permissions for detailed reference.

  If the database being restored does not exist, the user must have
  CREATE DATABASE permissions to be able to execute RESTORE. If the
  database exists, RESTORE permissions default to members of the
  sysadmin and dbcreator fixed server roles and the owner (dbo) of the
  database (for the FROM DATABASE_SNAPSHOT option, the database always
  exists).

  RESTORE permissions are given to roles in which membership information
  is always readily available to the server. Because fixed database role
  membership can be checked only when the database is accessible and
  undamaged, which is not always the case when RESTORE is executed,
  members of the db_owner fixed database role do not have RESTORE
  permissions.


Optimizing a "categorized" search table using trigrams I've been reading through this excellent answer to understand how pg_trgm works a bit, but I'm still unclear on the most efficient way of solving this query (efficient in terms of speed of search):

I have a table search that I run trigram searches on that looks like this:

Column      |  Type   | Modifiers
------------+---------+-----------
id          | bpchar  | collate C
user_id     | integer |
type        | text    |
search_on   | text    | collate C
data        | json    |
Indexes:
 "index_search_id" UNIQUE, btree (id)
 "index_search_search_on" gist (search_on gist_trgm_ops)
 "index_search_type" btree (type)
 "index_search_user_id" btree (user_id)

In this scenario, user_id is NULLable and type is also NULLable. The queries I'd run amount to these possibilities:

Search for rows (WHERE user_id = 123 OR user_id IS NULL) AND search_on % 'mystring'
Search for rows (WHERE user_id = 123 OR user_id IS NULL) AND type='my-type' AND search_on % 'mystring'

In plain words, I want all rows that have my user_id or NULL user_id, optionally are categorized by type, and match the term being passed in.

Right now I just have individual indexes on the 3 columns (as shown above) that can change based on the query. I understand however that a single index is generally more efficient.

Is it possible to use a single index that does trigram searches, but also exact match on user_id and type where they can optionally be NULL.

Is it possible to use a single index that does trigram searches, but also exact match on user_id and type where they can optionally be NULL.

Yes, NULL is included in indexes. And you can search for it like for any other value.

Yes, you can have a multicolumn trigram GiST index. But GiST indexes typically don't make sense for the data type integer. Btree indexes are better in every respect - except for your case of a multicolumn index. So Postgres does not install the required operator class by default. You need to install the additional module btree_gist first, once per database:

CREATE EXTENSION IF NOT EXISTS btree_gist;  -- only if not installed, yet

Then you can create your multicolumn index:

CREATE INDEX foo ON search USING gist (user_id, type, search_on gist_trgm_ops);

Related (with detailed instructions):

PostgreSQL EXCLUDE USING error: Data type integer has no default operator class
Optimizing queries on a range of timestamps (two columns)

And get operator precedence in your WHERE clause right:

WHERE (user_id = 123 OR user_id IS NULL)  -- parentheses!
AND    search_on % 'mystring'

Or:

WHERE (user_id = 123 OR user_id IS NULL)
AND   (type = 'my-type' OR type IS NULL)
AND    search_on % 'mystring'

Depending on data distribution, cardinalities, selectivity of predicates, cost settings etc. Postgres may still prefer an index on one (or two) column(s) (occasionally).


how to sort by timestamp field while counting duplicates I have a table with username, timestamp and action columns. I need to retrieve a list of usernames, total occurrences of each user and the latest timestamp for each user. The following statement works but it doesn't sort the timestamp field. I've tried adding "ORDER BY time DESC" but it has no effect. How can I retrieve the latest timestamp? The statement below returns the first occurrence of the timestamp.

user|time|action
jones|1-1-2016|actionA
jones|1-2-2016|actionA
jones|2-1-2016|actionB

SELECT user, time, COUNT(*) AS total
FROM table
GROUP BY user
HAVING total > 0

Expected Results:

jones|2-1-2016|3

Simply apply a MAX:

SELECT user, MAX(time), COUNT(*) AS total
FROM table
GROUP BY user
ORDER BY MAX(time) DESC

Of course, this assumes that time is actually a datetime or timestampand not a VarChar.


How does the table scan in an UPDATE WHERE predicated on an unindexed column work? I was looking into a deadlock caused by two concurrent UPDATE statements:

UPDATE [table]
SET [column] = 0
WHERE [unindexed_column] = @id

My understanding is that because the column the WHERE is predicated on is unindexed, a full table scan is performed. For each row, an update lock is acquired. If it matches the WHERE clause, it then upgrades to an exclusive lock and holds it until the statement completes.

The deadlock occurs when session A has an exclusive lock on row 2 and is trying to acquire an update lock on row 1, while session B has an exclusive lock on row 1 and is trying to acquire an update lock on row 2.

The reason for the deadlock makes sense, but I don't understand exactly how the table scan is performed that makes this scenario possible. If the two queries perform the scan in the same order, it seems like the worst case scenario is that one of the queries is blocked on acquiring an update lock until the other query completes and releases it's locks.

How is a table scan performed? Is the order the table rows are scanned in inconsistent? If the update statement fails to acquire an update lock, does it go to the next row and try the previous row again later? What, exactly, makes this deadlock possible?

Order of table scan is never guaranteed. Order in which rows are locked is not guaranteed either. In addition, SQL Server has lock escalation, so you can't really say what the engine decides to lock, row, page, or table itself.

Thus, deadlock may happen even if values of @id are different in concurrent sessions, but rows of interest happened to reside on the same pages. In SQL Server 2008 and higher you can add hint ROWLOCK which reduces chances of lock escalation (but again it's not guaranteed). If UPDATE can't acquire lock it waits (LOCK_TIMEOUT specifies how long it's gonna wait ).